Excel VBA: Turn On AutoFilter and Show Filter Arrows

To turn on AutoFilter in Excel VBA, call the AutoFilter method on the complete data range, including its header row. For example, use the macro below to show filter arrows on columns A through D in a list with headers in row 1; change the sheet name and range to match your workbook.

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

    ' Set the worksheet and include the header row in the range.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")

    ' With no filter criteria, AutoFilter displays the drop-down arrows.
    dataRange.AutoFilter
End Sub

AutoFilter lets people display only rows that meet selected conditions; it does not delete the rows that are hidden. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.

What AutoFilter does in Excel

AutoFilter adds a drop-down arrow to each column heading in a range or Excel table. A user can choose values, search the column, or set conditions such as “begins with” or “is greater than.” Excel hides rows that do not match the selected conditions. The source values remain in the workbook, and clearing the filter makes the rows visible again.

There are two related worksheet properties worth distinguishing. AutoFilterMode indicates whether filter arrows are displayed on the sheet. FilterMode indicates whether a filter is currently hiding rows. Arrows can be visible when no criteria are active, so these properties do not mean the same thing. In particular, turning on arrows does not itself filter the data.

For a normal range, the first row of the range is treated as the heading row. Include that row when specifying the range: if the headings are in row 1, use a range beginning at row 1. Avoid selecting only the heading cells if the purpose is to filter the records below them.

Turn on arrows without toggling them off

A no-argument call to Range.AutoFilter toggles the filter arrows for that range. That behavior makes a one-line macro convenient, but it also means running it twice can turn the arrows off. If a macro may be run repeatedly, check the worksheet state first. This basic version turns on arrows only when the sheet does not already have AutoFilter arrows:

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

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

    ' Do not call AutoFilter again if arrows are already present.
    If Not ws.AutoFilterMode Then
        dataRange.AutoFilter
    End If
End Sub

This guard is suitable when the worksheet has no other filtered range to manage. The worksheet-level check does not prove that the arrows belong to the range named in the macro. If another range already has AutoFilter enabled, the guarded version leaves that setup alone. For a routine that must ensure a particular range is the active AutoFilter range, compare the current range and replace it when necessary:

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

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

    ' Replace a different worksheet AutoFilter range if one exists.
    If ws.AutoFilterMode Then
        If ws.AutoFilter.Range.Address <> dataRange.Address Then
            ws.AutoFilterMode = False
            dataRange.AutoFilter
        End If
    Else
        dataRange.AutoFilter
    End If
End Sub

Replacing the range removes the old filter arrows before adding them to the target. If the target range is already active, the macro leaves it as it is, including any criteria already applied. Use this approach only when the macro is supposed to manage the worksheet’s one ordinary range AutoFilter.

Choose a range that matches the data

A fixed range such as A1:D100 is easy to understand, but it will not automatically include records added below row 100. If the list is a contiguous block with no blank rows or columns splitting it, CurrentRegion can find the surrounding block from a cell in the data. For example, set the range to ws.Range("A1").CurrentRegion when A1 is the top-left heading. Check the layout first: a completely blank row or column can cause CurrentRegion to stop early.

For a growing list, an Excel table is often more reliable than a fixed range. Tables expand as records are added, keep their headings, and have filter arrows by default. If a table already exists, VBA can explicitly show its arrows like this:

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

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

    ' Show the table's filter drop-down arrows.
    salesTable.ShowAutoFilter = True
End Sub

Replace SalesTable with the table’s actual name. To find or change a table name, select a cell in the table and use the Table Design tab in Excel for Microsoft 365 on Windows. A table’s filter controls belong to the table, so prefer its ShowAutoFilter property instead of treating it as an unrelated worksheet range.

Apply a filter criterion with VBA

Showing arrows and applying a condition are separate actions. The Field argument counts columns from the left edge of the AutoFilter range, starting at 1; it is not the worksheet’s absolute column number. Thus, if the range begins in column A, field 2 means column B. With headers in A1:D100, this example shows only records whose second column contains “West”:

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

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

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

If the range instead starts at column C, field 2 refers to column D. This relative numbering is a frequent source of filters being applied to the wrong field. Also, text criteria should match the kind of values in the column; filtering a date or number column may require criteria suited to those values rather than a text label.

Clear a filter or remove the arrows

To show all rows again while keeping the filter arrows available, use ShowAllData when a filter is active. The check for FilterMode avoids calling the method when there is no active filter:

Sub ClearActiveFilter()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Show all records but leave AutoFilter arrows in place.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

To remove ordinary worksheet AutoFilter arrows altogether, set AutoFilterMode to False. This is different from clearing criteria: it removes the controls as well. If the data is an Excel table, manage its arrows with the table’s ShowAutoFilter property. When building macros for sheets that can contain multiple tables, avoid assuming that one worksheet-level setting describes every table’s state.

Common problems and how to avoid them

The arrows disappear when the macro runs again. A no-argument AutoFilter call toggles arrows. Add a state check or use the table’s ShowAutoFilter property.
Only part of the list is filtered. The range may be too short, or a blank row or column may split a CurrentRegion selection. Confirm that the range includes every record and the heading row.
The wrong column is filtered. Recalculate Field from the first column of the selected range, not from the worksheet letter.
The macro reports that a worksheet or table cannot be found. Check that the tab name and table name match exactly. A worksheet’s visible tab name and a table’s name are separate identifiers.
The macro seems to do nothing. If arrows are already present, the guarded macro intentionally does not change them. Check the selected range and whether an existing AutoFilter belongs to a different range.
Records appear to have vanished. Filtering hides nonmatching rows. Clear the active filter to display them; do not assume the macro deleted data.

Run the macro safely on Windows and Mac

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the macro into the module. Return to Excel and run it from the Macros dialog, commonly opened with Alt+F8. Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA, and enable macros only for workbooks you trust.

On a Mac, the VBA editor and macro tools are also available, though keyboard shortcuts can differ by keyboard and Excel settings. Use the Developer tab or Excel’s macro tools if a Windows shortcut does not apply. Excel for the web can open a workbook containing VBA, but it cannot run VBA macros; open the file in the desktop Excel application to execute this code. The ribbon command Data > Filter is a manual alternative on desktop Excel when a macro is unnecessary.

Keep a backup before running code that changes a workbook. Excel’s normal Undo history does not reverse a VBA macro, so even a short macro can require restoring the saved copy if it changes the wrong range.

Frequently Asked Questions

How do I turn on AutoFilter for every column in a range?
Call AutoFilter on the complete range, including its header row. Excel adds a drop-down arrow to each column heading in that range.

How do I turn AutoFilter off with VBA?
For a normal worksheet range, set the worksheet’s AutoFilterMode property to False. For a table, set its ShowAutoFilter property to False.

Does turning on AutoFilter hide rows automatically?
No. Turning on the arrows makes filtering controls available. Rows are hidden only after a filter condition is applied.

How can I show all rows but keep the arrows?
If a filter is active, call ShowAllData on the worksheet. The arrows remain available so another filter can be selected.

Can I use AutoFilter on an Excel table?
Yes. Tables support filter arrows, and VBA can set the table’s ShowAutoFilter property. Tables are useful for lists that grow as new records are added.

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.