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;amp;amp;amp;gt;5000", _
     Operator:=xlAnd, _
     Criteria2:="&amp;amp;amp;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.
Additional examples can be found here.