Multiple Filters Question within VBA

[AlwaysLearning]

I need help putting 2 separate conditions for filtering a large spreadsheet. Each of the filters work well separately but I'm trying to figure a way to put them together so if either criteria cells or both are filled, the data would be filtered accordingly.

Here is a summary of my case: My spreadsheet has about 20 columns and about 20,000 rows. Column "C" (3rd column) has location # and Column N (14th column) has a regional director name. If I put a location number in cell D1, the filter below filters the data to show only the rows that match that location number from column C.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A6").CurrentRegion.AutoFilter field:=3, Criteria1:=Target.Value
End If
End Sub

If I want to filter by the name of the director, I filter below filters the data to show only the rows that match that director's name from Column N which would have multiple locations.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("J1")) Is Nothing Then
Range("A6").CurrentRegion.AutoFilter field:=14, Criteria1:=Target.Value
End If
End Sub

I'd like to combine both filters so if I put a location number in D1 and a director's name in J1, the data would be filtered to match both criteria.

Thanks in advance for your help

 

[Chihiro]

Advanced filter would be easiest way to accomplish this.
However, I wouldn't recommend for it to be fired on Worksheet_Change (though you could). Instead, assign as short cut key (I usually use Ctrl+Shift + d) to run macro.

You will need in D1 & J1, the column header for the column where you want to apply filter criteria (it must be exact match, so copy and paste).

You can leave E1~F1 empty or add additional column headers.

D2 & J2 you can enter your filter condition. If you leave one blank, you will filter using only one condition.

Code would be something like below.

Sub afInPlace()
    Sheet1.Range("A6").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("D1:J2"), Unique:=False
End Sub

 

[AlwaysLearning]

Thanks so much Chihiro for your quick reply.
The only issue is that this sheet is being sent to all our stores and their servers don't allow macros, that's the only reason I used the vba behind the scenes.

 

[Chihiro]

Ah I see. Something like this using Auto-filter method.

Error Handler is there to allow you to have just one criteria or both.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c1 As String
Dim c2 As String
c1 = Sheet1.Range("D1").Value
c2 = Sheet1.Range("J1").Value

If Len(c1) = 0 And Len(c2) = 0 Then
AutoFilterMode = False
MsgBox "You need criteria in $D$1 and/or $J$1"
Exit Sub
Else
On Error GoTo ErrHandler:
AutoFilterMode = False
Sheet1.Range("A6").CurrentRegion.AutoFilter Field:=3, Criteria1:="=" & c1
Sheet1.Range("A6").CurrentRegion.AutoFilter Field:=14, Criteria1:="=" & c2

End If

ErrHandler:
If Len(c1) = 0 Then
AutoFilterMode = False
Sheet1.Range("A6").CurrentRegion.AutoFilter Field:=14, Criteria1:="=" & c2
ElseIf Len(c2) = 0 Then
AutoFilterMode = False
Sheet1.Range("A6").CurrentRegion.AutoFilter Field:=3, Criteria1:="=" & c1
End If

End Sub

 

[jeffreyweir]

Any reason you can't just use a PivotTable? Or slicers? Or both? What version of Excel do you have?

 

[AlwaysLearning]

Chihiro, you're awesome!! your code worked like a charm. Thank you so much.

jeffreyweir, I was trying to make the filtering process as simple as possible for our location managers as they may not be Excel Savvy.

Thanks for all your help!