Hide specific rows when two dropdowns are selected

[Nick70]

Hi,

I have a sheet with two dropdowns.
Dropdown in cell C8 which has 3 options: Client, Partner, Friend
Dropdown in cell C13 which has 3 options: High, Medium, Low

I would like to have a macro so that if cell C8 has dropdown option 'Client' and cell C13 has dropdown 'Medium' (both conditions met) then all rows which have text in column A equal to 'HR' appear otherwise
if any other combination is chosen (e.g. Client, High or Partner, Low etc.) then all rows which have text in column A equal to 'HR' will disappear.

Basically I would like rows which have value equal to 'HR' in their first cell (so in column A) appear or disappear depending on which dropdown combination I choose.

Rows with 'HR' should be visible only for dropdown C8 = 'Client' and dropdown C13 = 'Medium' (both conditions must be valid at the same time) otherwise they should be hidden.

How do I do that?

Thanks,
Nix

 

[rollis13]

Well, since you haven't given an idea of how your data layout is I'll try to guess a macro. I'm supposing that you have headers in row 15 and your data from row 16 and down. Event Worksheet_Change is used to trigger. In a test file paste the macro in the sheet's module and adjust starting row (headers).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = "$C$8" And Not Target.Address = "$C$13" Then Exit Sub
    If Range("C8") = "Client" And Range("C13") = "Medium" Then
        With ActiveSheet
            AutoFilterMode = False
            With Range("A15", Range("A" & Rows.Count).End(xlUp)) '<- adjust starting row as needed
                .AutoFilter 1, Criteria1:="<>HR", VisibleDropDown:=False
                .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Hidden = False
            End With
        End With
    Else
        AutoFilterMode = False
    End If
End Sub