Hide cells based on 2 criteria

Question

I am trying to hide rows based off 2 different cells values. I have a value in cell B2 and want to hide anything in B5:B1000 not matching the criteria. I also have a value in cell D2 to further sort and want to hide anything in D5:D1000 not match the criteria in D2. To complicate it if a user enters "All" in either B2 or D2 I want to unhide their respective rows. I found VBA to hide based on B2, but am having adapting the code to also look at D2.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("B2"), Range(Target.Address)) Is Nothing Then


    Select Case Target.Value
    Case Is = "All": Rows("5:1000").EntireRow.Hidden = False
    Case Is <> "All": Rows("5:1000").EntireRow.Hidden = False
        For rownum = "5" To "1000"
            If Cells(rownum, 2).Value <> Range("B2") And Cells(rownum, 4).Value <> Range("D2") Then
                Cells(rownum, 2).EntireRow.Hidden = True
                Cells(rownum, 4).EntireRow.Hidden = True
            End If
        Next rownum
    End Select

End If


End Sub

Answer 1

As per my comment, you might want to give Range.AutoFilter a try. Imagine the following data:

Running the following code:

Sub Test()

Dim rng As Range

With Sheet1
    If .Cells(2, 2).Value = "ALL" Or .Cells(2, 4).Value = "ALL" Then
        If .FilterMode Then .ShowAllData
    Else
        .Range("A4:E10").AutoFilter 1, VisibleDropDown:=False
        .Range("A4:E10").AutoFilter 2, .Cells(2, 2), VisibleDropDown:=False
        .Range("A4:E10").AutoFilter 3, VisibleDropDown:=False
        .Range("A4:E10").AutoFilter 4, .Cells(2, 4), VisibleDropDown:=False
        .Range("A4:E10").AutoFilter 5, VisibleDropDown:=False
    End If
End With

End Sub

Results in:

You see how the VisibleDropDown parameter hides the dropdown-arrows. You can leave these lines (and parameters) if this isn't a big deal to your users. It's set to TRUE by default.