Hide rows, based on value of cell

In Excel

So - you want to hide some rows in a worksheet, based on the value in a cell. Or more than one cell.

We do that with VBA, here’s how.

• Get the range of rows, in this example from A7 to A100000.
• Adjust (here, by adding 6) to get the actual last row.
• For each value in that range - if the value in column A is equal to the value in cell G1 (Cells(1,7)), and the value three columns over (column D, c.Offset(0,3)) is equal to the value in cell G2 (Cells(2,7)), set the entire row to be hidden, else, set it to be shown.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Application.WorksheetFunction.CountA(Range("A7:A100000")) + 6
On Error Resume Next
For Each c In Range("A7:A" & LastRow)
If (c.Value = Cells(1, 7).Value And c.Offset(0, 3).Value = Cells(2, 7).Value) Then
c.EntireRow.Hidden = False
Else
c.EntireRow.Hidden = True
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub