VBA Set AutoFilter Range: Setup, Examples and Fixes

VBA & macros · Updated November 18, 2026

To set an AutoFilter range in Excel VBA, identify a rectangular range that includes the header row, then call its AutoFilter method—for example, ws.Range("A1:D100").AutoFilter. To change an existing range, clear its current filter and apply AutoFilter to the new range; you cannot assign a new value directly to AutoFilter.Range.

What the AutoFilter range includes

An AutoFilter range is the complete block of cells Excel should treat as a filtered list. Its first row must contain column headings, and the rows below it contain records. If the data occupies A1:D100, the range should include all four columns and every row, including the headings. A range that starts at row 2 omits the field names Excel needs for its dropdowns.

For a normal worksheet range, VBA exposes the current filtered block through the worksheet’s AutoFilter object. The range property is useful for inspecting what is already configured, but it is read-only. This will not work: ws.AutoFilter.Range = newRange. Instead, remove the old arrows and apply filtering to the replacement range.

A worksheet can have one ordinary range AutoFilter at a time. Excel tables are a related but distinct case: each table manages its own filter controls. That difference matters if a sheet contains multiple lists.

Set a fixed range in VBA

For a dataset whose size is known, use a direct address. The following Windows desktop Excel example clears an existing worksheet AutoFilter, then applies one to A1:D100:

Sub SetFixedAutoFilter()
    Dim ws As Worksheet
    Dim filterRange As Range

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

    ' Clear an earlier filter before applying a different range.
    If ws.FilterMode Then ws.ShowAllData
    If ws.AutoFilterMode Then ws.AutoFilterMode = False

    filterRange.AutoFilter
End Sub

Replace “Data” and the address with the actual worksheet name and data boundary. The first call to ShowAllData clears active criteria so rows hidden by filtering are shown again; the second removes the old dropdowns. Then filterRange.AutoFilter adds dropdowns to the new block. If there is no prior filter, the checks simply skip those cleanup actions.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the simplest way to protect the original data if a range or sheet name is wrong.

Choose a range that grows with the data

A fixed address is easy to understand but may miss records added later. One common alternative is CurrentRegion, which returns the contiguous block around a starting cell. With a header in A1, ws.Range("A1").CurrentRegion can capture a compact table without calculating its last row and column.

Use it only when the data is a solid block. A completely blank row or column can split the region, so a report with spacer rows may produce an incomplete filter range. The following alternative finds the last used row in column A and the last heading in row 1:

Sub SetDynamicAutoFilter()
    Dim ws As Worksheet
    Dim filterRange As Range
    Dim lastRow As Long
    Dim lastCol As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' Expect headings in row 1 and at least one record in row 2.
    If lastRow < 2 Or lastCol < 1 Then Exit Sub
    Set filterRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

    If ws.FilterMode Then ws.ShowAllData
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    filterRange.AutoFilter
End Sub

This approach assumes every record has a value in column A and that the headings occupy row 1. If column A can be blank for a valid record, choose a column that is always populated or determine the boundary another way. Likewise, if headings are on row 4, adjust both the last-column calculation and the range’s top row. A boundary check matters: without it, an empty sheet could lead the macro to filter a header-only or unintended range.

Change the range and apply criteria

When a filter already exists, determine the intended replacement range first. Clear the old criteria, turn off its dropdowns, and then apply the new range. Calling AutoFilter on a new range without considering the existing filter can leave you with confusing arrows or an unexpected range. For ordinary worksheet data, the cleanup-and-reapply pattern is the reliable way to change boundaries.

You can also apply a criterion as part of the AutoFilter call. In this example, the second column of A1:D100 is filtered to show “West”:

Sub FilterWestRegion()
    Dim ws As Worksheet
    Dim filterRange As Range

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

    If ws.FilterMode Then ws.ShowAllData
    If ws.AutoFilterMode Then ws.AutoFilterMode = False

    filterRange.AutoFilter Field:=2, Criteria1:="West"
End Sub

The field number is relative to the left edge of the filtered range, not the worksheet’s column number. For A1:D100, Field 1 means column A and Field 2 means column B. If the range instead starts at C1, Field 1 refers to column C. This is a frequent source of filters being applied to the wrong heading.

Filter different values and combine conditions

After the filter range is established, call its AutoFilter method with the field and criteria you need. For example, Criteria1:=">=1000" can show numeric values at least 1,000 in the chosen field. Text matching can use a literal value such as Criteria1:="West"; wildcard criteria are also available for text searches. Confirm that the field index points to the intended column before applying a criterion.

For a range of values, supply two criteria and an operator. For instance, a numeric field can be filtered between 100 and 500 using Criteria1:=">=100", Operator:=xlAnd, Criteria2:="<=500". The xlAnd operator requires both conditions to be true. Use xlOr when either condition should qualify. If you apply a new criterion to a field that already has a filter, decide whether to clear existing criteria first so old selections do not affect the result.

Use an Excel table when the list expands

If the data is an Excel table, its filter dropdowns are normally part of the table already, and the table can expand as rows are added. In VBA, refer to the table by its ListObject name rather than guessing a changing cell address. This example filters the table’s “Region” column to “West”:

Sub FilterTableByRegion()
    Dim ws As Worksheet
    Dim lo As ListObject

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

    ' Field is the table column's position, beginning at 1.
    lo.Range.AutoFilter Field:=lo.ListColumns("Region").Index, _
                        Criteria1:="West"
End Sub

Change “SalesTable” and “Region” to the actual table and column names. The column’s index avoids hard-coding its position, so the example continues to target the named field if columns are rearranged. If the goal is to extend the table itself, resize the ListObject with its Resize method; changing a worksheet AutoFilter range is not the same as resizing a table.

Common problems and how to fix them

  • The wrong column is filtered: Recount Field from the left edge of the selected range. It is not the worksheet column letter or number.
  • New rows do not appear: Check whether the macro uses a fixed endpoint such as row 100. Use a dynamic boundary or a table if the list regularly grows.
  • The range stops too early: CurrentRegion stops at blank rows or columns. Remove unintended gaps or calculate the last row and column explicitly.
  • Excel says the AutoFilter method failed: Confirm that the range is on the intended worksheet, includes a heading row, and is not an invalid or multi-area selection. Check worksheet protection and the exact sheet name too.
  • Old hidden rows remain: If a filter is active, call ShowAllData before removing its dropdowns. Be aware that rows hidden manually may still be hidden afterward; clearing filter criteria does not necessarily reverse every independent row-hiding action.
  • Headings are blank or duplicated: Give each field a clear, nonblank header. Meaningful headings make the dropdowns usable and reduce confusion when selecting fields in code.

Windows, Mac, and Excel for the web

The examples are intended for Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros; open the workbook in the desktop app to run or edit this code. Excel for Mac supports VBA, but Microsoft documents that the Range.AutoFilter method is not supported on Mac in the same way as Windows. For Mac-specific code, use a supported Selection or ListObject AutoFilter method and verify the behavior in the Excel version being used. The range logic and the importance of correct headers still apply.

On Windows, open the Visual Basic Editor with Alt+F11, then insert a standard module to add a procedure. On Mac, the shortcut can depend on the keyboard and system settings; use the Visual Basic Editor command from Excel’s Developer tools if the shortcut differs. A workbook containing VBA must be saved in a macro-enabled format such as .xlsm to retain its macros.

Frequently Asked Questions

How do I find the current AutoFilter range in VBA?
When the worksheet has an AutoFilter, inspect ws.AutoFilter.Range to get the range it covers. Check that ws.AutoFilterMode is True before relying on the AutoFilter object, because a worksheet with no filter has no current filter range to inspect.

Can I set AutoFilter.Range directly?
No. The range property identifies the existing AutoFilter range; it is not a writable way to change that range. Clear the old filter and call AutoFilter on the replacement range instead.

Does the AutoFilter range include the header row?
Yes. Include the header row as the first row of the range, followed by the records. Excel uses those headings to label each field’s dropdown.

Why does Field:=2 filter the wrong column?
Field is counted from the first column of the AutoFilter range. If the range begins in column C, Field 2 means column D, not worksheet column B.

Should I use CurrentRegion or a table?
Use CurrentRegion for a compact, uninterrupted block that may change size. Use an Excel table when the list is a structured dataset that regularly grows or when you want to refer to columns by name in VBA.

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.