AutoFilter based on Cell Value for novice VBA User

UDcc123

Board Regular
Joined
Feb 24, 2004
Messages
93
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

UDcc123

Board Regular
Joined
Feb 24, 2004
Messages
93
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,956
Messages
5,525,883
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top