VBA AutoFilter

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on October 25, 2023

In VBA, you can use Excel’s AutoFilter to filter a range of cells or an Excel table.

Note: If you want to learn how to use an advanced filter in VBA, click here: VBA Advanced Filter

Create AutoFilter in VBA

First, we will demonstrate how to AutoFilter a range, so a user can filter the data. The data that we will use in the examples is in Image 1:

vba autofilter data

 

Here is the code for creating AutoFilter:

Sheet1.Range("A1:E1").AutoFilter

In order to enable AutoFilter, we need to specify the header of the range, in our case A1:E1, and use the AutoFilter method of the object Range. As a result, our data range has filters activated:

vba autofilter enabled

Image 2. AutoFilter enabled for the data 

AutoFilter with Field and Criteria Parameters

VBA also allows you to automatically filter a certain field with certain values.

In order to do this, you have to use parameters Field and Criteria1 of the method AutoFilter. In this example, we want to filter the third column (Product) for Product A only. Here is the code:

Sheet1.Range("A1:E1").AutoFilter Field:=3, _
                                 Criteria1:="Product A"

In the Field parameter, you can set the number of the column in the range (not in Excel), while in Criteria1 you can put the value which you want to filter. After executing the code, our table looks like this:

vba autofilter field criteria

Image 3. AutoFilter with field and criteria

As you can see, only rows with Product A in the third column are displayed in the data range.

AutoFilter with Field and Multiple Criteria Values

If you want to filter one field with several values, you need to use the parameter Operator of the AutoFilter method. To filter multiple values, you need to set Operator to xlFilterValues and also to put all the values of Criteria in an Array. In this example, we filter the Product column for Product A and Product B. Here is the code example:

Sheet1.Range("A1:E1").AutoFilter Field:=3, _
                                 Criteria1:=Array("Product A", "Product B"), _
                                 Operator:=xlFilterValues

When we execute the code, we get only rows with Product A and Product B, as you can see in Image 4:

vba autofilter field multiple criteria

Image 4. AutoFilter with multiple criteria values

 

AutoFilter Data Range with Multiple Criteria

If you want to filter a field with multiple criteria, you have to use Criteria1 and Criteria2 parameters, but also the Operator xlAnd.

In the next example, we will filter the first column (Date) for dates in December 2018. Therefore, we have two criteria: a date greater than 12/01/18 and less than 12/31/18. This is the code:

Sheet1.Range("A1:E1").AutoFilter Field:=1, _
                                 Criteria1:=">=12/01/2018", _
                                 Operator:=xlAnd, _
                                 Criteria2:="<=12/31/2018"

When we execute the code, you can see that only dates in December are displayed in the data range:

vba autofilter multiple criteria

Image 5. AutoFilter with multiple criteria for the field

 

The Operator Parameter Values of AutoFilter method

In the next table. you can see all possible values of the Operator parameter of AutoFilter method and their descriptions:

Operator Description
xlAnd Includes multiple criteria – Criteria1 and Criteria 2
xlOr Includes one of the multiple criteria – Criteria1 or Criteria 2
xlTop10Items Filters a certain number of highest ranked values (number specified in Criteria1)
xlBottom10Items Filters a certain number of lowest ranked values (number specified in Criteria1)
xlTop10Percent Filters a certain percentage of highest ranked values (% specified in Criteria1)
xlBottom10Percent Filters a certain percentage of lowest ranked values (% specified in Criteria1)
xlFilterValues Includes multiple criteria values with Array
xlFilterCellColor Filters cells for colors
xlFilterFontColor Filters cells for font colors
xlFIlterIcon Filters icons
xlFilterDynamic Filter dynamic values

 

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples