VBA - how to hide rows if cell contains a specified value

Question

I'm fairly new to VBA and can't figure out the answer to this question by looking at other posts:

I want to use VBA to hide any rows where the text of cells A18:A153 equal the value "Hide" and also unhide any rows where these cells equal the value "Unhide". So, if cell A22 = "Hide" row 22 should be hidden. And if cell A23 = "Unhide" row 23 should be unhidden.

I tried this code to hide cells but it didn't work, so I didn't attempt unhiding cells:

With Worksheets("Report")
  For i = 18 To 153       
    If Cells(i, 1).Value <> "" And Cells(i, 1).Value = "Hide" 
      Then Cells(i, 1).EntireRow.Hidden = True        
    End If    
  Next i
End With

Thank you in advance!

score 1 · Accepted Answer · answered Sep 30 '20 at 21:01

You didn't use with and if-then correctly, the following code should work:

With Worksheets("Report")
  For i = 18 To 153    
    If .Cells(i, 1).Value <> "" And .Cells(i, 1).Value = "Hide" Then 
       .Cells(i, 1).EntireRow.Hidden = True    
    End If
  Next i
End With

VBA - how to hide rows if cell contains a specified value

1 Answers1