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...
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
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:
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
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: