Excel VBA Add AutoFilter: Code, Examples, and Fixes

VBA & macros · Updated November 21, 2026

To add AutoFilter with Excel VBA, identify the header-and-data range and call its AutoFilter method: dataRange.AutoFilter. For example, a macro can use Range("A1").CurrentRegion to find a contiguous data block and add filter arrows to its header row; the examples below show how to set that up, filter records, and avoid common pitfalls.

Quick setup: add filter arrows to a range

This macro targets the worksheet named Sales, finds the contiguous block of cells surrounding A1, and adds AutoFilter arrows. It first removes any existing worksheet-level AutoFilter, so use it when replacing the sheet’s current filter setup is intended. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.

Sub AddAutoFilterToSales()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Select the worksheet and its contiguous data block.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Remove an existing sheet-level AutoFilter before setting this one.
    If ws.AutoFilterMode Then
        If ws.FilterMode Then ws.ShowAllData
        ws.AutoFilterMode = False
    End If

    ' Add filter arrows to the first row of the selected range.
    dataRange.AutoFilter
End Sub

Change the worksheet name and starting cell to match your workbook. The first row of the range must contain the column headings. In Windows Excel, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, then run the macro with F5 while the cursor is inside it. The Mac Visual Basic Editor has a different menu and shortcut arrangement; use Tools > Macro > Visual Basic Editor or the Developer tab, depending on your Excel setup.

How AutoFilter works in VBA

AutoFilter adds drop-down controls to the header row so a user can show records matching selected conditions. It does not delete records or move them to another location: rows that fail the current criteria are hidden. Removing or changing a filter can reveal those rows again. This makes AutoFilter useful for reviewing a list without altering the underlying values.

In VBA, the method is called on a range, and its Field argument identifies a column by its position within that range—not by the worksheet’s absolute column number. For a range beginning in column C, Field:=1 means column C, and Field:=2 means column D. This relative numbering is a frequent source of filters being applied to the wrong field.

A range needs a clear header row and a consistent table-like layout. Blank rows or columns can split a block, and merged cells in the header area can make filtering unreliable. If the data is an Excel Table, its header row and data boundaries are managed as part of the table, which often makes it a better target for code that must keep working as the list grows.

Choose a reliable range

CurrentRegion is convenient when the data forms one uninterrupted rectangle. It expands from the selected cell until it reaches blank rows or columns. For a worksheet with headers in A1 and no blank separators inside the list, ws.Range("A1").CurrentRegion typically captures the headers and records together.

It is not a universal way to find data. A completely blank column within a report can split the region, while a note or total directly beside the list can cause the region to include extra cells. If the data has a known, stable size, specify the range explicitly, such as ws.Range("A1:F500"). If its row count changes, calculate the final row from a column that is always populated, then build the range with that last row. Check that the first row included is truly the header row.

  • Use CurrentRegion for a clean, contiguous block with no internal blank separators.
  • Use a defined address when the intended filter range is fixed and easy to identify.
  • Use a ListObject (Excel Table) when records are regularly added or removed.
  • Check for totals, notes, merged headings, and blank columns before applying a range-based filter.

Apply a filter criterion with VBA

Once the range has AutoFilter arrows, call the method again with a field number and criterion to filter a column. The example below assumes A1 is the top-left cell of a region whose third column contains region names. It shows only records where that field equals West.

Sub FilterSalesForWest()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' The range must include the header row and all records.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Field 3 means the third column within dataRange.
    dataRange.AutoFilter Field:=3, Criteria1:="West"
End Sub

The Criteria1 argument can be text, a number, or a date criterion. For example, a text criterion can be "West", while a numeric comparison can be written as ">=100". Excel’s criteria rules also support wildcard characters for text matching: an asterisk represents a sequence of characters, and a question mark represents one character. If the criterion itself includes a wildcard character, consult Excel’s AutoFilter criteria rules for escaping it.

You can filter more than one field by applying criteria to different field numbers. For example, filtering the Region field and then the Status field leaves rows that satisfy both field filters. To clear the current criteria while keeping the arrows, use ShowAllData when the sheet is in a filtered state:

Sub ClearSalesCriteria()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sales")

    ' Show all rows while leaving AutoFilter arrows in place.
    If ws.FilterMode Then ws.ShowAllData
End Sub

FilterMode indicates that the worksheet is currently displaying a filtered subset. AutoFilterMode indicates whether AutoFilter arrows are displayed. They are not interchangeable: clearing criteria with ShowAllData keeps the arrows, while setting AutoFilterMode = False removes the arrows. Keep that distinction in mind when writing a reset macro.

Use AutoFilter with an Excel Table

For a formatted Excel Table, VBA exposes the table as a ListObject. Tables automatically include filter arrows in their header row by default, and their ranges expand when records are added directly below them. This reduces the chance that a macro will miss newly appended records because it relies on a fixed address.

To apply a filter to a table column, identify the table by its name and use its Range. The field number is still relative to the left edge of that table range. Table names appear on the Table Design tab when the table is selected.

Sub FilterSalesTableForWest()
    Dim ws As Worksheet
    Dim salesTable As ListObject

    Set ws = ThisWorkbook.Worksheets("Sales")
    Set salesTable = ws.ListObjects("SalesTable")

    ' Filter the third table column to show West.
    salesTable.Range.AutoFilter Field:=3, Criteria1:="West"
End Sub

Replace SalesTable with the actual table name. A table column can also be referenced by its header in code that first finds that column’s index, which can make a macro easier to maintain if columns may be rearranged. However, the code must handle a missing or misspelled header rather than assuming the lookup always succeeds.

Remove arrows, clear criteria, or reset everything

These operations solve different needs. To show all records but retain the filter controls, use If ws.FilterMode Then ws.ShowAllData. To remove the worksheet’s AutoFilter arrows, use ws.AutoFilterMode = False. If you want the arrows back, apply AutoFilter to the intended range again.

Be cautious with a reset that turns off AutoFilter on a whole worksheet: a user may have set up a different filter range on that sheet. If the workbook contains multiple independent lists, avoid indiscriminately clearing the sheet’s existing filter before adding another. Consider using a named table and filtering that table’s range, or write logic that checks which range is already filtered and handles it deliberately. A macro should not silently discard a user’s chosen criteria unless resetting them is part of its stated purpose.

Common problems and practical fixes

  • The wrong column is filtered: Recount Field from the first column of the range. It is not necessarily the worksheet column number.
  • Headers are missing from the dropdown row: Make sure the selected range starts at the real header row and contains one header for each field.
  • Some records are not included: Check whether a blank row or column divided the CurrentRegion, or whether a fixed address stops before the latest records.
  • The macro says a worksheet name is invalid: Confirm the worksheet tab name matches the text in Worksheets("Sales"). The tab name is different from a worksheet’s VBA code name.
  • ShowAllData causes an error: It is only appropriate when a filter is active. Check FilterMode first, as in the example.
  • The arrows disappear after running the macro: Calling AutoFilter without criteria can toggle filter arrows for a range. Avoid repeatedly using a toggle-style setup without checking the existing state.
  • The macro will not run: Save the file as a macro-enabled workbook (.xlsm) if it must retain VBA, and allow macros only for files you trust. Excel for the web can open a workbook containing VBA but does not run VBA macros.

In Windows Excel, Ctrl+Shift+L toggles AutoFilter for the selected range. On Mac, Command+Shift+F is a commonly used equivalent; available shortcuts can vary with keyboard layout and Excel settings. The ribbon route is Data > Filter in Excel for Microsoft 365 on Windows. Excel for Mac has a similar Data tab, though the exact ribbon layout can differ. These interface controls are useful for manual filtering, but a VBA macro is more repeatable when the task must be automated.

When VBA is useful—and when it is not

Use VBA when a workbook needs to set up filters as part of a repeatable workflow, apply known criteria after importing data, or provide a button that prepares a report for a user. A macro can reduce repeated clicks, but it also introduces code that needs to be saved, enabled, and maintained. If users only need to filter occasionally, the built-in Data > Filter command or an Excel Table may be simpler and safer.

AutoFilter is also different from the worksheet function FILTER. AutoFilter hides nonmatching rows in the original list and is controlled through the interface or VBA. The FILTER function returns matching data as a dynamic array in the worksheet and is available in Microsoft 365 and Excel 2021 or later. Choose based on the outcome: interactive row hiding versus a formula-generated result in another location.

Frequently Asked Questions

How do I add AutoFilter to the entire used range?
Set a range to the header row’s CurrentRegion, then call dataRange.AutoFilter. Inspect the region first; blank rows and columns can make it smaller than the full list, and adjacent notes can make it larger.

Does VBA AutoFilter delete rows that do not match?
No. AutoFilter hides rows that fail the criteria; it does not delete their contents. Clearing the filter can show them again. Be careful not to confuse hiding filtered-out records with deleting data.

How do I filter by a column name instead of Field:=3?
Find the position of the desired header within the range, then pass that position as Field. The field number is relative to the filter range. For a table, you can locate the column by its header name before applying the filter.

How can I remove the filter arrows but keep every row visible?
If criteria are active, first call ShowAllData after checking FilterMode. Then set AutoFilterMode to False to remove the worksheet’s arrows. These steps are distinct from merely clearing criteria.

Can Excel for the web run an AutoFilter VBA macro?
No. Excel for the web supports workbook editing and ordinary filtering, but it does not execute VBA macros. Run the macro in the desktop Excel application; use the built-in filter controls in the browser when you do not need VBA automation.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.