## 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 canomitthe argument

If Operator isxlTop10Items, Criteria1 specifies thenumber 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 thefiltered 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 columnSub 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 criteriaHere, 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 criteriaWe 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” operatorSub 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 valuesSub 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 1Sub 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 ESub 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:UsingxlTop10PercentandxlBottom10PercentoperatorsSub 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 dataWhen 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.

## 1 thoughts on “Using Filters in Excel VBA – the Range.AutoFilter Method”