Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

VBA Code to Apply and Control AutoFilter in Excel

VBA Code Snippets

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
'Apply to a specific sheet by it's position to the left most tab.  1 being the first tab.
'Apply to a specific sheet by it's VBA Code Name
'VBA Code Name is the "(Name)" property of the worksheet
'Apply to a specific sheet in a different workbook.

AutoFilter Icons

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

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.

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

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), _
'Filter on no fill color
ActiveSheet.Range("$A$1:$A$7").AutoFilter Field:=1, Operator:=xlFilterNoFill

Clear an existing sort

'Clear the sorted field

Apply an alphabetical sort

'Clear the sorted field

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

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

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

'Applying the sort

Headshot Round

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:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

12 thoughts on “VBA Code to Apply and Control AutoFilter in Excel

  1. Melvin says:

    what macro code can i use if i want to filter using a cell reference?

    what macro code can i use if i want to filter everything except what is indicated in a cell?

    note : for these 2 samples, the values in the cells may change from time to time

    • Excel Off The Grid says:

      To filter on a cell reference it would be:

      Dim cellValue As Range
      Set cellValue = Sheets("Sheet2").Range("B1")
      ActiveSheet.Range("$A$1:$A$12").AutoFilter Field:=1, Criteria1:=cellValue

      To keep everything except the cell, it would be:

      Dim cellValue As Range
      Set cellValue = Sheets("Sheet2").Range("B1")
      ActiveSheet.Range("$A$1:$A$12").AutoFilter Field:=1, Criteria1:="<>" & cellValue
  2. Brad says:

    Similar to the above question, I want to filter 5 sheets by 1 criteria (number) that I enter into a cell on a 6th sheet named “Criteria”. I’ve got snippets of VBA for applying autofilter across worksheets, and filter active sheets, but I’m not sure how to combine into what I need.

  3. Andy says:

    Hi, I just want to refresh an already applied sorting. is there an easy way to do it?
    when I use AutoFilter, then sort, then filter something else or remove the filter, the sorting is completely scrambled, so I want to reapply the sort after filtering. I’m just using the normal sort functions provided by a table.

    • Excel Off The Grid says:

      Sorting is a destructive action – it will sort the cells on the worksheet and you cannot undo it (unless it’s in the list undo stack).

      Adding an Index column can help to get back to the original order – you just sort by the Index column at the end.

      I’m not sure why it would be scrambled – Are you applying the sort to all the cells in the Table?

  4. Stefan Bachevillier says:

    Hi, thanks for the article.
    How could we apply filters on different columns of a table with a “or” operator between the columns, not within one column only ?

    • Excel Off The Grid says:

      I don’t believe this is possible using filters.

      If you have the FILTER function in Excel 365 or Excel you should be able to achieve a similar outcome.

  5. Manuel Serafin Plasencia says:

    These tips are extremely didactic. I want first to thank you for sharing your knowledge. I am trying to filter a table by two columns (Fields), when I run the code and followed it step by step I got the first filter working right, but when the next one is applied then I can’t see any data. But If I apply the first filter using VBA code and then I manually apply the filter at the second column I got the right result.

    Could you give some advice about filtering a table from VBA with multiple columns (fields) filters acting simultaneously?

    Thanks in anticipation

    • Excel Off The Grid says:

      To filter by multiple columns, apply the filters individually.

      For example:

      ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:="=A", _
          Operator:=xlOr, Criteria2:="=D"
      ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:="=2"

      The code above filters the first field on values A or B, then filters the second field on value 2.

  6. Yashil Ghoorbin says:


    I currently have the following Sort code:
    ActiveWorkbook.Worksheets(“Non Rebate_Example”).AutoFilter.Sort.SortFields.Add Key:= _
    Range(“M2:M” & lrow2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    Column M has dates in them.
    It currently sorts from oldest to newest date.
    I would like to change it so that it sorts from oldest to newest but only for dates from 01/10/2017. So I want to leave any date before 01/10/2017 unchanged. How can I do that?

    • Excel Off The Grid says:

      Hi Yashil – If you’re only partially sorting the data set, then you would need to either:
      (1) Create a formula that creates an index column, then sort on the index column
      (2) Use VBA to loop through each row and then move it.

  7. Don Faison says:

    the last row in my range is a sum of all previous rows. I can sort the range manually to exclude the last row, but when I tried to to it with VBA (even when repeating the macro recorded by performing a manual sort), it always also includes the sum row in the sort, thus destroying the calculation. The line
    With ActiveWorkbook.Worksheets(wsname).AutoFilter.Sort does this, but I cannot add a range after the worksheet(…) because it creates an error. Is there anyway around this?

Leave a Reply

Your email address will not be published. Required fields are marked *