Auto Filter With Multiple Criteria in VBA

We will introduce how to use AutoFilter to filter a range of cells or an Excel table in VBA.

Auto Filter With Multiple Criteria in VBA

There are many situations in which we need to use filters in Excel while working on a VBA code. If we want to get the sales data for a specific date, we need to use a date filter on a date column in VBA.

VBA provides a solution for this problem using an AutoFilter.

We will start by understanding how to use the auto filter in a range so that a user can filter the data. Let’s have some data, to begin with, as shown below.

As shown below, we will use the Range method of the sheet to select the range on which we want to apply an AutoFilter.

# VBA
Sub test()
Sheet1.Range("A1:C1").AutoFilter
End Sub

Output:

We have to designate the header of the range, which in our case is A1:C1, and utilize the AutoFilter technique of the object range to apply the auto filter. This will result in the activation of filters for our range, as shown above.

Auto Filter With Field and Criteria in VBA

VBA also allows us to filter specific fields with particular values. To achieve this, we have to define the field we want to apply the filter.

We will also define the criteria based on which we want to apply a filter. If we wish to filter the second column for Laptop only, we can run the code as shown below.

# VBA
Sub test()
Sheet1.Range("A1:C1").AutoFilter Field:=2, Criteria1:="Laptop"
End Sub

Output:

As we can see from the above example, we were able to use the AutoFilter with the desired values by defining the values of the field and criteria. The result is shown above with the filter using the criteria we provided in our VBA code.

Auto Filter With Multiple Criteria in VBA

Suppose we want to set multiple criteria for our filter. In that case, we can do it by using another parameter operator in AutoFilter when we need to filter one field with multiple values.

We can use the xlFilterValues operator and pass the values to the Criteria parameter in an array to filter out based on these values, as shown below.

# VBA
Sub test()
Sheet1.Range("A1:C1").AutoFilter Field:=2, Criteria1:=Array("Laptop", "iPhone"), _
                                 Operator:=xlFilterValues
End Sub

Output:

As we can see from the above example, when we run the code, the AutoFilter is activated with multiple values and displays the results we wanted.

Auto Filter Date Range With Multiple Criteria in VBA

If we want to use AutoFilter based on the date range, or if we’re going to get the data between a specific date range, we have to use multiple Criteria parameters with an xlAnd operator.

Let’s go through an example and add more data to our sample sheet with the dates below.

As you can see from the above data, we have added a new column with dates. As shown below, we will use these dates to filter the data between the 20th and 25th of May.

#VBA
Sub test()
Sheet1.Range("A1:D1").AutoFilter Field:=1, Criteria1:=">=05/20/2022", Operator:=xlAnd, Criteria2:="<=05/25/2022", Operator:=xlFilterValues
End Sub

Output:

As we can see from the above example, we can easily filter the data in the date range by providing two criteria of dates and using the Operator parameter with the xlAnd value.

Some Operator parameters and their description are described below.

1. The xlAnd operator can filter with multiple criteria such as Criteria1 and Criteria2.
2. The xlOr operator can filter with either one criteria or the other.
3. The xlTop10Items operator will help us filter the particular number of the highest-ranked values from the criteria.
4. The xlBottom10Items operator will help us to filter the particular number of the lowest-ranked values from the criteria.
5. The xlTop10Percent operator will help us to filter the particular percentage of the highest-ranked values from the criteria.
6. The xlBottom10Percent operator will help us to filter the particular percentage of the lowest-ranked values from the criteria.
7. The xlFilterValues operator will help us to filter the criteria with multiple values.
8. The xlFilterCellColor operator will help us to filter the criteria based on cell colors.
9. The xlFilterFontColor operator will help us to filter the criteria based on font colors.
10. The xlFIlterIcon operator will help us to filter the criteria based on font icons.
11. The xlFilterDynamic operator will help us to filter the dynamic values.