VBA: How to Use AutoFilter with Multiple Criteria

You can use the following methods to use AutoFilter in VBA with multiple criteria:

Method 1: Use AutoFilter with Multiple Criteria in One Column

Sub FilterMultipleCriteria()

With Range("A1:C11")
    .AutoFilter Field:=1, Criteria1:=Array("A", "C"), Operator:=xlFilterValues
End With

End Sub

This particular macro will filter all rows in the range A1:C11 where the value in the first column is equal to either “A” or “C”.

Method 2: Use AutoFilter with Multiple Criteria in Multiple Columns

Sub FilterMultipleCriteria()

With Range("A1:C11")
    .AutoFilter Field:=1, Criteria1:="A"
    .AutoFilter Field:=2, Criteria1:="Guard"
End With

End Sub

This particular macro will filter the range A1:C11 to only display rows where the first column is equal to “A” and the second column is equal to “Guard”.

The following examples show how to use each of these methods in practice with the following dataset in Excel:

Example 1: Use AutoFilter with Multiple Criteria in One Column

Suppose we would like to filter the dataset to only display rows where the first column is equal to either “A” or “C”.

We can create the following macro to do so:

Sub FilterMultipleCriteria()

With Range("A1:C11")
    .AutoFilter Field:=1, Criteria1:=Array("A", "C"), Operator:=xlFilterValues
End With

End Sub

When we run this macro, we receive the following output:

Notice that the dataset has been filtered to only show the rows where the first column is equal to either “A” or “C”.

Note: We only included two possible values in the Array() function but feel free to include as many as you’d like.

Example 2: Use AutoFilter with Multiple Criteria in Multiple Columns

Suppose we would like to filter the dataset to only show rows where the value in the Team column is equal to A and the value in the Position column is equal to Guard.

We can create the following macro to do so:

Sub FilterMultipleCriteria()

With Range("A1:C11")
    .AutoFilter Field:=1, Criteria1:="A"
    .AutoFilter Field:=2, Criteria1:="Guard"
End With

End Sub

When we run this macro, we receive the following output:

Noticed that the dataset has been filtered to only show rows where the value in the Team column is equal to A and the value in the Position column is equal to Guard.

Note: You can find the complete documentation for the VBA AutoFilter method here.