## Using Filters in Excel VBA – the Range.AutoFilter Method

*in*Excel

In this article we will look at the Range.AutoFilter method of Excel VBA. It is a very powerful tool that is not used as often as is should be by programmers.

Suppose you have a list of data tagged by month want to do certain operations on only the rows for the month of January. Instead of iterating through all the rows (that could well run into thousands) and checking if the month is January on each row, you can simply apply a filter, to return only the rows corresponding to January and then do the further processing. That would save a huge amount of time and improve the performance considerably.

Let’s begin with the syntax:

**Syntax**

expression . AutoFilter( Field , Criteria1 , Operator , Criteria2 , VisibleDropDown )

**Example**

Sheet1.Range("B:E").autoFilter _ field:=2, _ Criteria1:="Jan", _ Operator:=xlOr, _ Criteria2:="Feb", _ VisibleDropDown:=True

**where** –

**expression:** An expression that returns a Range object on which filtering is to be done – Columns B to E in the example

**Field:** The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one) – In the example field 2 corresponds to the second column in the range i.e. Column C

**Criteria1:** The criteria – Is equal to “Jan”, in the example

**Note:**

For blank fields, use **“=”**

For nonblank fields, use **“<>”**

For All, you can **omit** the argument

If Operator is **xlTop10Items** , Criteria1 specifies the **number of items**

**Operator:** One from the XlAutoFilterOperator list specifying the type of filter. – xlOr in our example. We will see the entire list shortly.

**Criteria2:** The second criteria which is used with Criteria1 and Operator to construct a compound criteria. – **“Feb”** in our case

**VisibleDropDown: **True to display the AutoFilter drop-down arrow for the **filtered field**. False to hide it for the filtered field. True by default.

All the arguments in this method are optional. If you omit all of them, this method simply toggles the display of the AutoFilter drop-down arrows in the specified range.

Let’s begin with the examples. For all the examples, including the one we already saw, we will be using the below table:

**Example 1: **Single criteria on a single column

Sub autoFilterSingle() Sheet1.Range("B:E").autoFilter _ field:=3, _ Criteria1:="Boston", _ VisibleDropDown:=True End Sub

Filters column D to include only Boston city as shown below

Note, if you enter a field value > 4 (as our range contains only 4 columns), you will get an error.

**Example 2:** Using “xlAnd” operator for two criteria

Here, we will filter column E to include only those values that lie between the specified range

Sub autoFilterAnd() Sheet1.Range("B:E").autoFilter _ field:=4, _ Criteria1:="&amp;amp;amp;amp;amp;amp;gt;5000", _ Operator:=xlAnd, _ Criteria2:="&amp;amp;amp;amp;amp;amp;lt;8000", _ VisibleDropDown:=True End Sub

The output will look like this

**Example 3**: Using “xlOr” operator for two criteria

We have already covered this example. So, here is the code and output

Sub autoFilterOr() Sheet1.Range("B:E").autoFilter _ field:=2, _ Criteria1:="Jan", _ Operator:=xlOr, _ Criteria2:="Feb", _ VisibleDropDown:=True End Sub

**Example 4:** Using operator **“****xlFilterValues” **with Array for multiple **“Or”** criteria

Sub autoFilterValues() Sheet1.Range("B:E").autoFilter _ field:=2, _ Criteria1:=Array("Sep", "Apr", "Jun", "Nov"), _ Operator:=xlFilterValues, _ VisibleDropDown:=True End Sub

Here we are filtering once those months that have 30 days. So, the output will be

**Example 5: **Using “**xlFilterCellColor**” operator

Sub autoFilterColor() MsgBox Sheet1.Range("B3").Interior.ColorIndex Sheet1.Range("B:E").autoFilter _ field:=4, _ Criteria1:=RGB(204, 255, 204), _ Operator:=xlFilterCellColor, _ VisibleDropDown:=True End Sub

This returns all rows with the specified color. The filtered rows will look like this

Similarly, you can use the **“xlFontColor” **operator to filter on the font color.

**Example 6: **Using “xlFilterDynamic” operator to get rows with above average values

Sub autoFilterDynamic() Sheet1.Range("B:E").autoFilter _ field:=4, _ Criteria1:=xlFilterAboveAverage, _ Operator:=xlFilterDynamic, _ VisibleDropDown:=True End Sub

Here is the result of the executed code.

**Example 7: **Using “xlFilterDynamic” operator to get dates that lie in Quarter 1

Sub autoFilterDynamic2() Sheet1.Range("B:E").autoFilter _ field:=2, _ Criteria1:=xlFilterAllDatesInPeriodQuarter1, _ Operator:=xlFilterDynamic, _ VisibleDropDown:=True End Sub

Note that the column C is a date with format “mmm”. The result will be

There are many more dynamic filter criteria. For a complete list of dynamic operators available, you can refer to “XlDynamicFilterCriteria enumeration”

**Example 8: **Using “xlTop10Items” operator to get top 3 values for column E

Sub autoFilterTopX() Sheet1.Range("B:E").autoFilter _ field:=4, _ Criteria1:=3, _ Operator:=xlTop10Items, _ VisibleDropDown:=True End Sub

Here is the output showing the top 3 values. Note that, criteria 1 specifies the number of rows you want to select.

Similar to the above example, you can use the **“xlBottom10Items”** operator to get the bottom x values.

**Example 9:** Using **xlTop10Percent** and **xlBottom10Percent** operators

Sub autoFilterTopXPercent() Sheet1.Range("B:E").autoFilter _ field:=4, _ Criteria1:=30, _ Operator:=xlTop10Percent, _ VisibleDropDown:=True End Sub

Here the criteria specifies the percentage value.

Example 10: **Show all the data**

When the data in the sheet is already filtered and you try to run any of the examples above, the new filter will be applied on the filtered results. So, effectively, you will now have 2 filters in place. At some times, this is required while at other times, you may need to clear the existing filters before applying a new one. For that use this simple code below:

Sheet1.ShowAllData

Note that Excel for Mac does not support this method.

Refer “VBA Range Overview : Learn to Use One of the Most Commonly Utilized Worksheet Tools” for more such tools.

Thanks so much, David!