VBA Code to Apply and Control AutoFilter in Excel

AutoFilters are a great feature in Excel.  Often they are a quicker way of sorting and filtering data than looping through each cell in a range.

This post provides the main lines of code to apply and control the AutoFilter settings with VBA.

Adapting the code to your needs

Every code snippet below is applied to the ActiveSheet (i.e., whichever sheet which is currently in use at the time the macro runs).  It is easy to apply the code to other sheets by changing the section of code which refers to the ActiveSheet.

'Apply to a specific sheet by name
Sheets("SheetName").AutoFilter...

'Apply to a specific sheet by it's position to the left most tab.  1 being the first tab.
Sheets(1).AutoFilter...

'Apply to a specific sheet by it's VBA Code Name
'VBA Code Name is the "(Name)" property of the worksheet
Sheet1.Autofilter...

'Apply to a specific sheet in a different workbook.
Workbooks("AnotherWorkbook.xlsx").Sheets("Sheet1").AutoFilter...

AutoFilter Icons

When using AutoFilters, the icons at the top of the columns indicate whether any settings have been applied.

Check Auto Filter existence

Each worksheet can only contain one AutoFilter.  The following code checks for the existence of an AutoFilter by checking the AutoFilterMode property of the sheet.

'Check if an AutoFilter already exists
If ActiveSheet.AutoFilterMode = True Then

    'Do something

End If

Add / Remove an Auto Filter

'Apply filter to 'Current Region' which contains cell A1.
ActiveSheet.Range("A1").AutoFilter

The AutoFilter will be applied to the “current region” of the cells.  The Current Region represents the cells surrounding the selected cell which are not separated by a blank row or column.

Trying to add an AutoFilter to an empty cell will trigger an error message.

'Remove AutoFilter
ActiveSheet.AutoFilterMode = False

Hide / Display Auto Filter drop-down button

The drop-down buttons can be hidden, giving the appearance that there is no AutoFilter on the worksheet.  This is great if using VBA to control the AutoFilter as part of a process; the user will not be able to apply their own settings.

'Hide the dropdown filter from Cells by field number, or by range
ActiveSheet.Range("A1").AutoFilter Field:=1, Visibledropdown:=False
ActiveSheet.Range("A1").AutoFilter Field:=2, Visibledropdown:=False

'Display the dropdown filter from Cells by field number, or by range
ActiveSheet.Range("A1").AutoFilter Field:=1, Visibledropdown:=True
ActiveSheet.Range("A1").AutoFilter Field:=2, Visibledropdown:=True

Count visible records

After applying a filter, counting the visible cells in the first column will show the number of records meeting the criteria applied.

'Count the number of rows which are visible in the AutoFilter
'including the Header (hence the -1 at the end)
MsgBox ActiveSheet.AutoFilter.Range.Columns(1). _
    SpecialCells(xlCellTypeVisible).Count - 1

Get Auto Filter range

The code below will show you the range of cells which are covered by the AutoFilter.

'Get Range of AutoFilter, including the header row
MsgBox ActiveSheet.AutoFilter.Range.Address

Show everything

Showing everything in the AutoFilter will cause an error if a filter has not been applied.

'First check if a filter has been applied
If ActiveSheet.FilterMode = True Then

    'Show all the data
    ActiveSheet.ShowAllData

End If

Apply text filter to a column

The example below shows how to apply a text filter for any value of “A” or “B”.

'Apply a text filter to a column
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:="=A", _
    Operator:=xlOr, Criteria2:="=B"

Advanced filtering is one of the most useful features of AutoFilter.  The examples show how to apply different criteria by using Wildcards.

Equals: Criteria1:="=Apple"
Does Not Equal: Criteria1:="<>Apple"
Begins with: Criteria1:="=*Apple"
Ends with: Criteria1:="=Apple*"
Contains: Criteria1:="=*Apple*"
Does Not Contain: Criteria1:="<>*Apple*"

Operators allow multiple filters to be applied to a single column.  Filters can be ‘and’ where both criteria are met, or ‘or’ where either criteria is met.

Or: Operator:=xlOr
And: Operator:=xlAnd

Apply color filter to a column

AutoFilters allow cells to be filtered by color.  The RGB color code below can be used to sort by any color.

'Apply color filter using an RGB color
ActiveSheet.Range("$A$1:$A$7").AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), _
    Operator:=xlFilterCellColor

'Filter on no fill color
ActiveSheet.Range("$A$1:$A$7").AutoFilter Field:=1, Operator:=xlFilterNoFill

Clear an existing sort

'Clear the sorted field
ActiveSheet.AutoFilter.Sort.SortFields.Clear

Apply an alphabetical sort

'Clear the sorted field
ActiveSheet.AutoFilter.Sort.SortFields.Clear

'Setting the sorting options
ActiveSheet.AutoFilter.Sort.SortFields.Add Order:=xlAscending, _
    SortOn:=xlSortOnValues, Key:=Range("A1:A7")
'Applying the sort
ActiveSheet.AutoFilter.Sort.Apply

To apply descending sort, change the Order property to xlDescending:

Order:=xlDescending

Apply a custom sort order

Alphabetical and reverse alphabetical may be the most likely sort order, however any custom sort order can be applied.  The example below will sort in the order of “L”, “J”, “B” then “Q”.

'Clear the sorted field
ActiveSheet.AutoFilter.Sort.SortFields.Clear

'Set the sort to a Custom Order
ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("A2:A7"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, _
    CustomOrder:="L,J,B,Q"

'Applying the sort
ActiveSheet.AutoFilter.Sort.Apply

---

What next?

Discover how you can automate your work with our Excel courses and tools.