Using Filters in Excel VBA – the Range.AutoFilter Method
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:=Truewhere –
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 itemsOperator: 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 SubFilters 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;gt;5000", _ Operator:=xlAnd, _ Criteria2:="&amp;amp;amp;amp;amp;amp;amp;amp;lt;8000", _ VisibleDropDown:=True End SubThe 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” criteriaSub autoFilterValues() Sheet1.Range("B:E").autoFilter _ field:=2, _ Criteria1:=Array("Sep", "Apr", "Jun", "Nov"), _ Operator:=xlFilterValues, _ VisibleDropDown:=True End SubHere 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 SubThis 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 SubHere 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 SubNote 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 SubHere 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 SubHere 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.ShowAllDataNote 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.
One thought on “Using Filters in Excel VBA – the Range.AutoFilter Method”