Excel VBA: How to Enable AutoFilter

To enable AutoFilter with VBA, select the worksheet range that includes the column headers and run its AutoFilter method: Worksheets("Data").Range("A1").CurrentRegion.AutoFilter. This displays filter arrows for that contiguous data range; the macro below shows how to do it without accidentally toggling an existing filter or changing a different range.

What enabling AutoFilter does

AutoFilter adds drop-down arrows to the header row of a range. Users can use those arrows to show or hide rows according to values, text, dates, colors, or other available criteria. Enabling the feature does not itself choose a criterion, sort the data, or delete rows. It makes the filtering controls available.

In VBA, Range.AutoFilter is the method used to apply AutoFilter to a range. Calling it with no arguments turns the arrows on if they are not already displayed, but it can also turn them off if they are already displayed for that range. That toggle behavior is why a macro that may run repeatedly should check the worksheet’s current state instead of blindly calling the method every time.

AutoFilter is different from a worksheet’s AutoFilterMode and FilterMode properties. AutoFilterMode reports whether filter arrows are displayed on the worksheet. FilterMode reports whether the worksheet is currently showing a filtered subset of its rows. Arrows can be present while no criteria are applied, so the two properties answer different questions.

Enable a filter for a header-based range

For a simple list, put the headers in the first row of the data and use CurrentRegion to identify the block around a starting cell. For example, if a worksheet named Data has headers in A1 and uninterrupted records beneath them, the following macro enables AutoFilter when needed. It leaves an existing filter in place rather than toggling it off.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' A filter range needs a header row and at least one data row.
    If dataRange.Rows.Count < 2 Then
        MsgBox "The range needs a header row and at least one data row."
        Exit Sub
    End If

    ' Avoid toggling off a filter that is already displayed.
    If Not ws.AutoFilterMode Then
        dataRange.AutoFilter
    End If
End Sub

CurrentRegion expands from A1 until it reaches a completely blank row or column. It is convenient for compact lists, but it may include adjacent notes or stop early if the data has gaps. If the intended range is known and stable, specifying it directly, such as ws.Range("A1:F250").AutoFilter, can be clearer. Adjust the sheet name, starting cell, and range to match the workbook; worksheet names must match exactly.

Choose the right range and header row

The range passed to AutoFilter should include the header row and all columns and records that belong to the list. If the headers are in row 3 and the list runs from columns B through G, the filter range should begin at B3, not at B4. Starting one row too low makes the first record look like a header and removes it from the filterable records.

For reliable results, use one header row with a distinct label for every column. Avoid blank header cells, merged cells in the header, and blank rows or columns inside the data block. These layouts can make the filter range ambiguous or produce confusing field names. A clean rectangular data set is easier for Excel and for readers to interpret.

Field numbers in AutoFilter criteria are relative to the range’s left edge, not to worksheet column letters. If the filter range starts at column B, then its first field is B, its second field is C, and so on. This matters when you later add criteria in code: a criterion for the fourth column of that range uses Field:=4, even if that column is E on the sheet.

Use a safer macro when the worksheet may already have a filter

The simple macro above leaves any existing worksheet filter alone. That is usually safer than removing it automatically, but it also means it will not replace a filter attached to a different range. If a worksheet contains several lists or macros, check what is already filtered and decide how the procedure should behave before modifying the range.

This version stops with a message if a filter already exists on a different range. It exits quietly when the intended range already has its arrows, and applies AutoFilter only when the worksheet has no filter arrows.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

    If dataRange.Rows.Count < 2 Then
        MsgBox "The range needs a header row and at least one data row."
        Exit Sub
    End If

    If ws.AutoFilterMode Then
        If ws.AutoFilter.Range.Address = dataRange.Address Then
            Exit Sub
        Else
            MsgBox "A filter is already attached to a different range."
            Exit Sub
        End If
    End If

    dataRange.AutoFilter
End Sub

This check compares the address of the existing AutoFilter range with the desired range. If a workbook uses multiple sheets, the check is scoped to the Data worksheet because the code sets ws to that sheet. If you want the macro to replace a different range’s filter, make that a deliberate choice: removing arrows can also discard the current filter setup. Save a copy before running a macro, because VBA actions cannot be reversed with Ctrl+Z.

Excel Tables are often the better option

If the list is an Excel Table, its filter arrows are managed by the table rather than by a loose worksheet range. Tables automatically extend as records are added and keep their header and data boundaries together. In VBA, access the table through the worksheet’s ListObjects collection and set its ShowAutoFilter property to True.

Sub EnableTableAutoFilter()
    Dim ws As Worksheet
    Dim tbl As ListObject

    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("SalesTable")

    ' Display the table's filter arrows.
    tbl.ShowAutoFilter = True
End Sub

Replace SalesTable with the actual table name, which you can find by selecting a table and viewing its name on the Table Design tab. A table can have filter arrows even when no criteria are active. If the table name is wrong, VBA raises an error because that ListObject cannot be found. Use a table when the data is a growing, maintained list; use a range when the layout is fixed or the cells are not structured as a table.

Enable arrows, apply criteria, and clear filtering

Enabling AutoFilter alone does not hide anything. To filter by a particular value, pass criteria to AutoFilter after identifying the field relative to the filter range. For example, a range beginning at A1 with a Region header in column C has Region as its third field. The criteria syntax depends on the data type and the comparison you want, so it is useful to keep the range and field mapping explicit.

To show all rows again while keeping the arrows, test FilterMode before calling ShowAllData. This distinction is useful in reusable macros: clearing criteria should not necessarily remove the filter controls.

Sub ClearActiveFilterCriteria()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Restore rows hidden by filter criteria; keep the arrows.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

By contrast, setting ws.AutoFilterMode = False removes the worksheet’s filter arrows. It is not the same as showing all records, and it should not be used as a substitute for clearing criteria when the goal is to leave the filter controls available. Before changing an existing filter, consider whether someone has deliberately selected criteria that should be preserved.

Run the macro in Microsoft 365 for Windows

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. Replace the example sheet and range names, then return to Excel. You can run a selected procedure from the editor with F5, or run it from Excel by choosing Developer > Macros, selecting its name, and choosing Run. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain its VBA code. The standard .xlsx format does not store VBA macros. Excel may show a security warning when opening a workbook that contains macros; only enable macros in files from a source you trust. A macro that adds filter arrows does not need internet access or elevated permissions, but any macro should be reviewed before it is run.

You can also turn AutoFilter on or off from Data > Filter, or use Ctrl+Shift+L on Windows. These are useful for a one-time change, while VBA is useful when a workbook needs to set up the filter repeatedly. In Excel for Mac, use Data > Filter; keyboard shortcuts can differ by Excel release and keyboard layout. The Visual Basic Editor is available on Mac, but its menus and shortcut keys differ from Windows, so use the Mac ribbon command or the Mac editor’s own menu rather than assuming Windows keystrokes apply.

Common problems and how to fix them

The arrows appear in the wrong row: Check that the selected range begins with the real header row. A range starting on the first data record treats that record as the header.
The macro seems to turn the filter off: A no-argument call to AutoFilter can toggle arrows. Check AutoFilterMode first, as in the examples.
The range stops too early: CurrentRegion stops at a completely blank row or column. Remove unintended gaps or specify the exact range instead.
New records are not included: A fixed address such as A1:F250 does not automatically grow with entries beyond row 250. Use an Excel Table or calculate the intended range based on the actual data.
The macro reports a subscript error: Confirm that the worksheet name in Worksheets("Data") is exact and that the code is running in the workbook that contains that sheet.
A table macro cannot find the table: Verify the ListObject name in Table Design. A worksheet name and a table name are separate identifiers.
The code runs but no rows disappear: That is expected if the macro only enables arrows. A filter criterion must be applied separately to hide rows.

If a macro runs without an obvious result, check the active worksheet and the actual range address before changing code. Also check whether rows are hidden for reasons other than AutoFilter. Hidden rows, grouped rows, and filtered-out rows can look similar on screen but do not all respond to the same VBA method.

Frequently Asked Questions

How do I turn on AutoFilter for every column in Excel VBA?
Apply AutoFilter to the full rectangular range, including its header row and all intended columns. Excel then provides a drop-down for each field in that range; the method does not require a separate call for every column.

Does Range.AutoFilter toggle the filter?
Calling Range.AutoFilter without arguments can toggle the arrows for that range. To make a macro safe to run more than once, check the worksheet’s AutoFilterMode property before calling it, or use an Excel Table and set ShowAutoFilter to True.

How can I show all rows but keep the filter arrows?
If the worksheet is currently filtered, call ShowAllData after checking FilterMode. This clears the active criteria while leaving the filter controls available.

Can I use AutoFilter on an Excel Table?
Yes. Tables have their own filter controls. In VBA, refer to the table through Worksheet.ListObjects and set its ShowAutoFilter property to True.

Can I undo an AutoFilter macro with Ctrl+Z?
No. Excel’s undo history does not reverse VBA macro actions. Save a copy of the workbook before running a macro, particularly if it may alter existing filters or other worksheet content.

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.