Hide Rows that <> "string"

[dellzy]

Hi,
I found below formula in this forum that works really great on hiding all rows if it matches the string in any column of the row. However, I would like it to be reversed. Meaning, it will hide all rows that is NOT match to the string and also NOT blank (to avoid hiding rows of the entire worksheet). Also it needs to apply from row 5 then below. What I want to achieve is that I want to create a validation list consists of Dept name above row 5 and a table below it will only show the row records for the particular dept only. This is to avoid other users from other dept to meddle with records for other depts. Anyone can help,please? Thank you in advance.


Sub hiderows() For i = 1 To Range("A" & Rows.Count).End(xlUp).Row k = 0 For j = 1 To Range("A" & i).End(xlToRight).Column If InStr(1, Cells(i, j), "Circuit Total") > 0 Then k = k + 1 End If Next j If k > 0 Then Rows(i).Select Selection.EntireRow.Hidden = True End If Next i End Sub</pre>

 

[skorpionkz]

check this file. I changed the code so it will run any time the Value in cell A4. Please note that this change affect the location of the code as well (it is in Sheet Module)

https://www.dropbox.com/s/sjyekqat7h8xxqg/HideRows.xlsm

 

[dellzy]

Hi,
Can anyone help me, please? Any other solutions, pls?

Thank you in advance.

 

[dellzy]

check this file. I changed the code so it will run any time the Value in cell A4. Please note that this change affect the location of the code as well (it is in Sheet Module)

https://www.dropbox.com/s/sjyekqat7h8xxqg/HideRows.xlsm

Hi ,
Wow! Tqvm for the solution! It works! Just a bit of adjustment I want to seek for your kind assistance. I need to make it exact match because there are 2 different depts but have certain chars in the spelling are the same. E.g. we have OS (Ops Support) & Policy Services (POS) depts. When I enter/select "OS", it also includes those of "POS".

Thank you in advance.

 

[skorpionkz]

Hi ,
I need to make it exact match because there are 2 different depts but have certain chars in the spelling are the same. E.g. we have OS (Ops Support) & Policy Services (POS) depts. When I enter/select "OS", it also includes those of "POS".

No prob just change:

lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlPart).Column

to

lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlWhole).Column

Just one think to keep in mind.
As you ask to check whole row for the requested string, i used loop in the code. This is not best solution as with bigger data base is slow.

The PeteRooneyHome's suggestion would work much faster but then you need to tell which column contains Deps.

 

[dellzy]

No prob just change:

lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlPart).Column

to

lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlWhole).Column

Just one think to keep in mind.
As you ask to check whole row for the requested string, i used loop in the code. This is not best solution as with bigger data base is slow.

The PeteRooneyHome's suggestion would work much faster but then you need to tell in which column contains Deps.

Hi skorpionkz ,

Perfecto! U're a genius! Thanks a million! I'll take note on the lengthy number of rows will slower d process but the max it would go would be 1000 rows if we are to limit this to keep for a year 1 template. Thanks a lot!

 

[dellzy]

Hi,

I have presented this to the team and received very positive response. Tq so much to skorpionkz. However, we would like to enhance it a little bit, which is to add another col called month. So, we wish to add another criteria on the hide/unhide rows, which are based on dept and month. Can help me on the addition macro commands, pls?

Thank you in advance.

 

[skorpionkz]

Few questions:
1. Do you want to hide row when both condition are met (ex. POS and March)
2. In which column you would have Month
3. In which cell you would add Month value to hide
4. Is there any column which always have value in it (after row 5)?

 

[skorpionkz]

5. What format Column with month would have? Just month in word (like "March"), the date (01/03/2014)?

 

[Killavirus]

Hey skorpionkz
many thanks for your code, works a charm

is there anyway to get it to only work in a specific range, I am finding it take a little time to run and hangs excel for a few seconds.
I dont need to scan empty rows

Many thanks.

 

[skorpionkz]

As I already mentioned this code is not too fast. It is better to use solution proposed by PeteRooneyHome's.

Check example under below link:

to dellzy:
Check this link. I change code to use Autofilter instead of just hiding the rows. it works for the months. In orange cell just input month number or name (not the date). the month column is F not (column 6) so if you need to change it for your code just change (Field:=6) to appropriate column number. Also if you would input month to different cell that A3 then code need to be adjusted.

https://www.dropbox.com/s/kpgw5t3bgspmr3l/HideRows2.xlsm