Turn On AutoFilter in VBA: Code and Examples

To turn on AutoFilter with VBA, select the data range, including its header row, and call its AutoFilter method. For example, Range("A1").CurrentRegion.AutoFilter adds filter arrows to the contiguous data region around cell A1 if they are not already displayed.

What AutoFilter Does

AutoFilter adds dropdown arrows to a table’s header row so users can display only rows matching selected values or conditions. It does not change or delete the underlying records: it hides rows that do not match the active criteria. In VBA, the AutoFilter method can display the arrows, apply a filter, or change existing filter settings, depending on the arguments you provide.

The range used for a regular worksheet filter should include the headers and the records beneath them. If the first row of the selected range contains data rather than field names, Excel treats those values as headers, which can make the dropdown labels confusing. A blank row or column inside the dataset can also split what Excel recognizes as a data region, so the range you specify matters.

For everyday use, it helps to distinguish two states:

Worksheet.AutoFilterMode reports whether filter arrows are displayed on the worksheet.
Worksheet.FilterMode reports whether the worksheet is currently hiding rows because a filter is applied.

A sheet can have arrows but no active criteria. It can also have an active filter while the arrows remain visible. This distinction is important when writing code to show all records or remove filtering entirely.

Basic VBA to Turn On Filter Arrows

This macro targets a worksheet called Data and uses the contiguous region around A1. If the sheet already has filter arrows, it leaves the existing filter setup alone rather than trying to toggle it off.

Save a copy of the workbook before running any macro. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the safest way to recover from an unintended change.

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

    ' Use the worksheet and data block that begin at A1.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Add arrows only if this sheet does not already show them.
    If Not ws.AutoFilterMode Then
        dataRange.AutoFilter
    End If
End Sub

Change Data to the exact worksheet tab name. ThisWorkbook means the workbook containing the macro; use ActiveWorkbook only when you intentionally want to act on whichever workbook is currently active. The latter can be risky if users have several workbooks open.

CurrentRegion is convenient when the data is a solid rectangle with no fully blank rows or columns separating it from nearby content. For a fixed dataset, an explicit range such as ws.Range("A1:F250") is more predictable. If the range changes over time, consider turning the data into an Excel Table and referring to that table instead.

Choosing the Right Data Range

The filter range determines which columns receive dropdowns and which rows are included. Include one header row and all records, but avoid including unrelated notes, totals, or a second block of data. If a totals row is included, Excel may treat it as an ordinary record and filter it away.

For example, if row 1 contains fields from columns A to D and records continue through row 120, use ws.Range("A1:D120"). With CurrentRegion, make sure A1 is inside the dataset and that no completely blank row or column breaks the block. A blank cell within a row is not by itself a boundary; an entirely empty row or column is the concern.

When the number of records changes, an Excel Table is often a better long-term choice than a hard-coded address. Tables expand as users add rows, carry field names, and provide built-in filter controls. Their structured names also make VBA easier to understand than a range whose final row has to be calculated each time.

Apply a Filter Criterion with VBA

Calling AutoFilter with arguments applies a criterion to a field. The Field number is relative to the left edge of the filter range, not the worksheet’s column number. In a range beginning in column A, Field 3 is column C; in a range beginning in column D, Field 3 is column F.

This example filters the third field of the region around A1 to show records whose value is Open:

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

    ' The first row of this region must contain field headers.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

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

Excel generally adds filter arrows when a criterion is applied to a suitable range, so a separate call to turn on arrows is not always necessary. If you have an existing filter on a different range, however, applying a criterion to another range can lead to confusing behavior. Keep the filter range consistent, and avoid relying on an existing worksheet state that the macro has not checked.

Text criteria are commonly supplied as strings. For example, Criteria1:="West" matches the value West in the selected field. Criteria can also use comparison operators, such as Criteria1:=">=100" for numeric values at least 100. Wildcard and date filtering have additional rules, so test those criteria against representative data, especially when dates or numbers may be stored as text.

Clear Criteria or Remove the Arrows?

These are different operations. To show every record again while keeping the dropdown arrows, use ShowAllData only when the sheet is actually filtered. Checking FilterMode first avoids calling the method when there are no hidden rows due to a filter.

Sub ClearWorksheetFilterCriteria()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Restore all rows but leave the filter controls available.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

To remove the arrows from an ordinary range filter, set the worksheet’s AutoFilterMode property to False. This removes the filter controls and discards the current filter setup, rather than merely displaying all records. It is not a way to turn arrows on: use the range’s AutoFilter method for that.

Sub RemoveWorksheetFilterArrows()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Remove the worksheet's AutoFilter arrows, if present.
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If
End Sub

Use the clear-criteria approach when users should continue filtering manually. Remove arrows only when the interface should no longer offer filtering or when a macro needs to rebuild the range. For a formatted Excel Table, use its table-specific properties and methods rather than assuming that removing a worksheet filter is the right way to manage the table.

Using AutoFilter with an Excel Table

A Table, also called a ListObject in VBA, normally has filter controls in its header row. If the workbook contains a table named SalesTable, its filter range can be addressed directly rather than discovered from a cell. This example makes the table’s filter buttons visible and filters its second table field:

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

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

    ' Show the table's filter buttons and filter its second field.
    salesTable.ShowAutoFilter = True
    salesTable.Range.AutoFilter Field:=2, Criteria1:="West"
End Sub

Replace the worksheet and table names with those in your workbook. The table’s field numbering starts at its first column, even if that column is not column A on the sheet. A table can also expand when rows are added, which reduces the chance that new records fall outside a fixed filter range.

Common Problems and How to Avoid Them

Wrong header row: Include the real field-name row at the top of the range. Avoid selecting a title or blank row as the header.
Wrong field number: Count from the first column of the filter range, not from worksheet column A.
Unexpected range: CurrentRegion stops at fully blank rows or columns and may include adjacent content. Use a known range or a Table where appropriate.
Trying to toggle repeatedly: A bare Range.AutoFilter call can toggle filter arrows in some contexts. Check AutoFilterMode before adding arrows if the goal is to ensure they are present.
ShowAllData error: Do not call it blindly. Check FilterMode, because arrows can be visible even when no criteria are currently filtering records.
Protected sheet: Protection can prevent filtering, depending on the sheet’s protection settings. If users need to filter a protected sheet, configure protection to allow filtering or arrange the macro’s protection workflow deliberately.
No visible change: The criterion may match every row, or the selected field may contain values in a different format than expected. Check the field index and sample cell values.

A further misconception is that turning on AutoFilter sorts the records. It does not: it adds filtering controls, and applying a criterion hides nonmatching rows. Sorting is a separate operation. Similarly, filtering does not permanently remove rows; clearing the criteria makes them visible again.

Excel for Mac, Excel for the Web, and Macro Safety

The examples above target Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros; use the desktop Excel application to execute them. On Mac, Microsoft documents a limitation for the Range.AutoFilter method; the documentation notes that similar methods on Selection and ListObject objects are supported. Mac behavior can depend on the method and Excel version, so test a copy of the workbook. To filter manually in Excel for Mac, use the Data tab’s Filter control.

VBA macros may be blocked by security settings, and users should not enable macros in files they do not trust. Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA. Before running code that changes filters or sheet settings, make a backup: Ctrl+Z does not undo a macro’s actions. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, the shortcut differs by keyboard and configuration, and the editor can also be opened from Excel’s Developer tools when available.

Frequently Asked Questions

How do I turn on AutoFilter for a specific range?
Set a Range variable to the header-and-data block, then call dataRange.AutoFilter. For example, a range explicitly set to A1:D120 includes four fields and the records through row 120.

How can I turn on AutoFilter without toggling it off?
Check Worksheet.AutoFilterMode first. If it is False, call the range’s AutoFilter method; if it is already True, leave it alone unless you deliberately want to change the existing filter.

How do I remove a filter but keep the dropdown arrows?
When criteria are active, call Worksheet.ShowAllData after checking that Worksheet.FilterMode is True. This shows all rows while leaving the filter controls in place.

How do I remove AutoFilter arrows with VBA?
For an ordinary worksheet filter, set Worksheet.AutoFilterMode = False when arrows are present. This removes the controls; it is different from clearing criteria and showing all records.

Why does Field:=3 filter the wrong column?
The field number is counted from the first column in the selected filter range. If the range begins in column C, Field 3 refers to column E, not worksheet column C.

Can I run VBA AutoFilter in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel to run the macro, or use the web interface’s filter controls manually.

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.