VBA AutoFilter Not Working? Common Fixes

VBA & macros · Updated November 18, 2026

If VBA AutoFilter is not working, first check that the macro targets the correct worksheet and a contiguous range with a header row; then confirm that the Field number is counted from the left edge of that range. In Excel for Microsoft 365 on Windows, AutoFilter is applied with the range’s AutoFilter method, but a wrong range, mismatched criteria, or an existing filter state can make the result look broken.

What VBA AutoFilter Does

AutoFilter hides rows that do not meet one or more criteria; it does not delete those rows or change their values. The visible filter arrows let someone change the criteria manually, while VBA can set criteria as part of a repeatable task. For example, a macro can show only orders marked “Open” or values above a threshold.

In a worksheet range, the Field argument is a position within the range, not the worksheet’s column number. If the filter range is A1:D100, Field 1 means column A, Field 2 means B, and Field 3 means C. If the range starts in column C, Field 1 means C. This is one of the most common reasons a macro runs without producing the expected filtered view.

Filtering also depends on the shape of the data. A simple range should have one header row, with records below it and no completely blank column splitting the data. AutoFilter hides entire worksheet rows, so unrelated information elsewhere on those same rows may disappear from view too.

Check the Range, Header, and Field Number

Before changing code, identify the exact top-left and bottom-right cells of the intended data, including the header row. Check that the header text is in the first row of that range and that the column being filtered is inside it. A range that starts at A2, for instance, excludes the headers in row 1 and shifts every field number.

  • Range: Is the intended list included, without unrelated rows or columns?
  • Header: Is the first row of the range a row of column names, rather than a data row?
  • Field: Does the field number count from the range’s left edge?
  • Data: Are values in the target column consistently stored as text, numbers, or dates?

For example, if your headers are in A1:D1 and Status is in column C, use Field 3. If the filtered range instead begins at B1, Status is Field 2. A useful debugging step is to select the range in Excel and verify its columns against the code before investigating less common causes.

A Basic Macro That Applies a Filter

This example assumes a worksheet named Data, headers in A1:D1, records below them, and a Status field in column C. Column A must contain a value on every record so that the macro can find the last row reliably. Before running any macro, save a separate copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

Sub FilterOpenOrders()
    ' Assumes headers in A1:D1 and records below them.
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim filterRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if the sheet contains a header but no records.
    If lastRow < 2 Then Exit Sub

    Set filterRange = ws.Range("A1:D" & lastRow)

    ' Clear existing criteria without removing the filter arrows.
    If ws.FilterMode Then ws.ShowAllData

    ' Field 3 is column C because the range starts in column A.
    filterRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub

To use it, change the sheet name, range columns, and field number to match the workbook. In Windows Excel, open the Visual Basic Editor with Alt+F11, insert a standard module, paste the procedure, and run it from the editor or the Macros dialog. On a Mac, Option+F11 (sometimes Fn+Option+F11, depending on the keyboard) opens the editor; menus and shortcuts can vary slightly by keyboard and Excel release. Save a macro-enabled workbook as .xlsm if you need to retain the code.

The example checks FilterMode before calling ShowAllData, because that method is intended to reveal rows hidden by an active filter. It does not remove the filter arrows. If the sheet has no active criteria, the macro skips that call and applies the requested criterion.

Criteria Must Match the Cell Values

A criterion can be valid VBA syntax and still fail to match the data. Text criteria must match the underlying cell content, including spaces and punctuation. If a cell contains “Open ” with a trailing space, an exact criterion of “Open” may not match it. Similarly, values that look like numbers but are stored as text can behave differently from numeric values.

Examples of common criteria include an exact text match, a numeric comparison, and a wildcard text search. In each case, the criterion goes with the field position for the target column:

  • Criteria1:="Open" matches the text Open.
  • Criteria1:=">1000" asks for numeric values greater than 1000.
  • Criteria1:="*west*" uses asterisks as wildcards to match text containing west.
  • Criteria1:="=" filters for blank cells; Criteria1:="<>" filters for nonblank cells.

For two conditions on one field, AutoFilter can use an operator and a second criterion. For example, applying a lower and upper bound to a numeric column requires Operator:=xlAnd and two criteria. For dates, first confirm that the cells contain real Excel date values rather than date-looking text. Date display formats can differ by region, so test date criteria on a copy and use a consistent date source instead of assuming that a typed date string will be interpreted identically on every computer.

Existing Filters, Tables, and Hidden Rows

A worksheet may already have filter arrows or criteria from a previous run. AutoFilterMode reports whether filter arrows are displayed, while FilterMode indicates whether rows are currently filtered. These describe different states. If the goal is to reset criteria, use ShowAllData only when the sheet is actually in FilterMode; turning arrows off and clearing criteria are not the same operation.

Excel tables, also called ListObjects, have their own filter arrows and expand as records are added. If the data is already a table, referencing the table directly can be more dependable than calculating a range each time. For a table named Orders, the table’s data range is available through ListObjects("Orders").Range; the field number still counts from the first table column. Avoid filtering a table and a separate overlapping range as if they were independent lists.

Also check whether rows were hidden manually or by another macro. Clearing an AutoFilter criterion does not necessarily restore rows hidden for unrelated reasons. If the displayed result seems incomplete, inspect row hidden states and confirm that the data has not been sorted or moved in a way that puts records outside the selected range.

Common Errors and How to Diagnose Them

A run-time error often points to a bad worksheet reference, an invalid range, or a method being used in an inappropriate state. A sheet name must match the tab name exactly, and the workbook containing the code may not be the workbook containing the data. Using ThisWorkbook means the workbook where the macro is stored; ActiveWorkbook means the workbook currently active in Excel. Choose deliberately.

  • The macro finishes but nothing changes: Check the field index, criterion spelling, target sheet, and whether the selected range includes the records.
  • Only some records are included: Look for blanks that split the data, an outdated last-row calculation, or records beyond the selected range.
  • ShowAllData causes an error: Call it only when FilterMode is True; arrows may exist even when no criteria are active.
  • The wrong column is filtered: Recount the field from the left edge of the range, not from worksheet column A.
  • The filter appears to ignore values: Inspect spaces, inconsistent data types, formulas returning empty strings, and text-formatted numbers.

For diagnosis, add a breakpoint in the Visual Basic Editor or step through the macro with F8 on Windows. Inspect the values of lastRow, the address of filterRange, and the worksheet name immediately before the AutoFilter line. A temporary message box showing the range address can also reveal whether the macro selected what you expected. Remove diagnostic code after confirming the cause.

When AutoFilter Is Not the Right Tool

AutoFilter is suited to interactively viewing matching rows in place. It is not a replacement for extracting a separate report, deleting unwanted records, or calculating a summary. Advanced Filter can copy matching rows to another location, while a PivotTable is generally better for grouping and summarizing data. If the requirement is to return matching values into a formula-driven result area, Excel’s FILTER function is another option in Microsoft 365 and Excel 2021 or later.

VBA macros do not run in Excel for the web. The browser version can display a workbook containing macros, but running or editing VBA requires the desktop Excel application. Excel for Mac supports VBA, though some features and available controls can differ from Windows. If a workbook must work for people using the web version, consider a non-VBA approach or an Office Script where that platform and workflow support it.

Frequently Asked Questions

Why does VBA AutoFilter use the wrong column?
Because Field is relative to the leftmost column in the filter range. If the range is B1:E100, Field 1 refers to column B, not column A or worksheet column 1.

How do I remove an AutoFilter criterion without removing the arrows?
When a filter is active, call ShowAllData on the worksheet to show all records while leaving the filter arrows available. Check FilterMode first so the macro does not call it when no criteria are active.

Why does AutoFilter return no results even though the text looks right?
Look for leading or trailing spaces, different punctuation, wildcard characters, and values stored as a different data type. Compare the actual cell content with the criterion rather than relying only on how the cell is displayed.

Can I run a VBA AutoFilter macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run its VBA code, or choose a web-compatible alternative if browser execution is required.

Can I undo a VBA filter macro with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. A filter itself only hides rows, but save a backup before running macros that may also alter or delete data.

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.