Find a cell and hide row

[misrael]

I'm sure this is very easy, but I'm struggling with it.

The word "Test" appears several places within column C from C1 to C60. I want to hide all of the rows where this happens.

Any help is greatly appreciated. Thanks.

 

[dfenton21]

Hi There,

You can use the autofilter. Select "Custom" from the drop down list.
In the "Custom AutoFilter" dialog box, choose "does not equal" from the first list, and select "Test" from the list beside it.

Any further questions, please let me know

Damien.

 

[misrael]

Thanks, but I'm looking for VBA code that can do it.

I'm assuming the best way is using a loop with a find statement, but everytime I write it, I get errors or it doesn't do what I intend.

 

[meldoc]

Hello

Try.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">With</SPAN> Worksheets(1).Range("C1:C60")<br>  <SPAN style="color:#00007F">Set</SPAN> c = .Find("Test", LookIn:=xlValues)<br>  <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    firstAddress = c.Address<br>    <SPAN style="color:#00007F">Do</SPAN><br>      c.EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>      <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)<br>      <SPAN style="color:#00007F">If</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> firstAddress<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[misrael]

You're a genius. Works great.
Thanks.

 

[misrael]

Actually I have one other issue with this.

I have more code after this loop that I want executed, but when this loop is complete it exit's the sub.

How do I change it so it just exits this loop and continues on with the sub.

Thanks.

 

[shg]

Change

If c Is Nothing Then Exit Sub

to

If c Is Nothing Then Exit Do

 

[misrael]

Perfect! Thanks.