Hide/Unhide Rows using VBA...

[Matty]

Hello All,

I am trying to devise some code that will hide the entire Row in my Worksheet if certain criteria is met.

I have the following so far, which was based on some code posted by jonmo1, in response to another person's post:

Private Sub test()
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each C In Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
Rows(C.Row).EntireRow.Hidden = C.Value = 1
Next C
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

It works great by hiding any Rows where the value in Column C equals 1. However, I would like to tweak the code so that it would hide Rows based on more than one criteria. For example, if the value in Column C was 1 OR 2, then hide the Row. If any other value, then the Row should remain visible.

Can anyone tell me how I can tweak the code to achieve this, please?

Thanks,

Matty

 

[Domski]

Hi,

Try:

Private Sub test()
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each C In Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
If C.Value = 1 Or C.Value = 2 Then
 
    Rows(C.Row).EntireRow.Hidden = True
 
Else
 
    Rows(C.Row).EntireRow.Hidden = False
 
End If
Next C
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Dom

 

[Peter_SSs]

Matty

Here is another option you might consider:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> test()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    ActiveSheet.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>    Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).AutoFilter _<br>        Field:=1, Criteria1:="<>1", Operator:=xlAnd, Criteria2:="<>2"<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Matty]

Thanks Domski and Peter_SSs. I'll give both these a go to see what suits best.

Cheers,

Matty