AutoFilter based on Cell Value for novice VBA User

[UDcc123]

I'll start by saying I'm a novice VBA user, but can copy/paste/modify existing code well enough

- On worksheet "B", I have a table of data with:
- Row 1 = Title row
- Row 2 = Column Headers (State, City, Metric1, Metric2, Metric3)
- Rows 3-5000 = Data

- On worksheet "C", I have a table of data with:
- Row 1 = Title row
- Row 2 = Column Headers (State, City, Metric4, Metric5, Metric6)
- Rows 3-8000 = Data

- I have both worksheets B & C set up on AutoFilter

- Because there are hundreds of cities in the data and it's a pain to use the auto-filter dropdown, I'd like to do the following instead:
- On worksheet "A", put "State" in Cell A1 and have the user type the State they want to see in Cell A2
- They would then go back to worksheets B & C and see only rows with that State
- I'd also put "City" in cell B1 and have the user type the City they want to see in Cell B2
- The AutoFilter on worksheets B & C would filter when either A!A2 or A!B2 is populated (double filter if both are populated)
- If A!A2 or A!B2 are not populated, then the corresponding column would not be filtered

 

[UDcc123]

I think I figured it out...might not be pretty...but it works...I plugged the code below into worksheet A. If A2 or B2 are blank, then their corresponding AutoFilters are removed, else the autofilters are applied to the values in A2/B2 for both worksheets B & C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A2") = vbNullString Then
Sheets("B").Range("A3:E10000").AutoFilter Field:=2
Else: Sheets("B").Range("A3:E10000").AutoFilter Field:=2, Criteria1:="=" & Range("A2")
Sheets("B").Select
End If

If Range("B2") = vbNullString Then
Sheets("B").Range("A3:E10000").AutoFilter Field:=1
Else: Sheets("B").Range("A3:E10000").AutoFilter Field:=1, Criteria1:="=" & Range("B2")
Sheets("B").Select
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If Range("A2") = vbNullString Then
Sheets("C").Range("A3:AK3000").AutoFilter Field:=2
Else: Sheets("C").Range("A3:AK3000").AutoFilter Field:=2, Criteria1:="=" & Range("A2")
Sheets("C").Select
End If

If Range("B2") = vbNullString Then
Sheets("C").Range("A3:AK3000").AutoFilter Field:=1
Else: Sheets("C").Range("A3:AK3000").AutoFilter Field:=1, Criteria1:="=" & Range("B2")
Sheets("C").Select
End If

End Sub