Excel VBA AutoFilter: Examples, Criteria, and Fixes

Excel VBA AutoFilter filters a worksheet range or Excel table by applying criteria to one or more columns. On Windows, the key method is Range.AutoFilter: specify the range, the column number within that range, and the criterion you want to show.

What AutoFilter Does

AutoFilter adds drop-down filter controls to a header row and hides rows that do not match the selected conditions. It does not delete or rearrange the underlying records. For example, filtering an order list to show only rows with a status of Open leaves the other orders in the worksheet; they are simply not displayed until the filter is changed or cleared.

A VBA filter uses the same basic idea as the Filter command on Excel’s Data tab, but a macro can repeat the action consistently. This is useful for preparing reports, narrowing a large list before processing it, or letting a button show a particular category of records. A filter is not a substitute for sorting: it controls which records are visible, while sorting changes their order.

The column argument, called Field, is counted from the left edge of the range supplied to AutoFilter—not necessarily from worksheet column A. If the range begins in column B, its first field is B, its second is C, and so on. That detail explains many macros that filter the wrong column.

Apply a Basic Filter

The example below assumes a worksheet named Data, headers in A1:D1, and records below them. Column C contains the status. It finds the last occupied row in column A, then filters the third column of the A:D range to show Open records. If your workbook has important records with blank values in column A, use a column that is reliably filled to find the last row.

Before running any macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so test code on a copy or on a small sample first.

Sub FilterOpenOrders()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim filterRange As Range

    ' Use the worksheet containing the order list.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Row 1 contains headers; stop if there are no data rows.
    If lastRow < 2 Then Exit Sub
    Set filterRange = ws.Range("A1:D" & lastRow)

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

To use the code, open the Visual Basic Editor with Alt+F11 in Excel for Windows, choose Insert > Module, and paste the procedure into the module. Run it from the editor or assign it to a form control. The workbook must be saved in a macro-enabled format, such as .xlsm, if you want to keep the VBA code.

How Fields and Criteria Work

Criteria1 sets the main condition. A text criterion such as Open matches the cell value; comparison criteria can select numbers or dates. For example, two conditions can show amounts that are at least 100 but less than 500. In this example, the data range is A1:D100, column D contains numeric amounts, and field 4 therefore refers to that amount column.

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

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

    ' Keep amounts from 100 up to, but not including, 500.
    filterRange.AutoFilter Field:=4, Criteria1:=">=100", _
        Operator:=xlAnd, Criteria2:="<500"
End Sub

Use xlAnd when both conditions must be true, as in the numeric interval above. Use xlOr when either condition can be true, such as a value below 100 or above 500. Wildcards are useful for text: an asterisk represents any number of characters, while a question mark represents one character. For instance, a criterion of North* can match North, Northeast, or North Region. Excel’s ordinary AutoFilter text matching is not case-sensitive.

To include several exact values from one field, VBA can pass an array with the xlFilterValues operator. Use this when the desired values form a list rather than a simple pair of conditions. Confirm that the array items match the actual cell values, including spaces; a trailing space in a source cell can make an apparently correct filter return no rows.

Date criteria deserve extra care because a date’s displayed format depends on regional settings, even though Excel stores dates as numbers. When writing date filters, prefer criteria based on valid Excel date serial values or build them with VBA date functions rather than relying on an ambiguous string such as 1/2/2025. For a specific report, also decide whether the end date should be included; a less-than condition on the following day can avoid excluding records with times later in the final day.

Filter an Excel Table

For a regularly maintained list, converting the data to an Excel Table is often more reliable than hard-coding a range. Tables expand when new rows are added and keep their header and filter controls together. In this example, a table named SalesTable is on the Sales worksheet, and its second table column contains region names.

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

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

    ' Field 2 means the second column of this table.
    salesTable.Range.AutoFilter Field:=2, Criteria1:="West"
End Sub

Create or rename a table from the Table Design tab, where its name is shown in the Table Name box. Using the table’s own range makes the macro less dependent on a fixed final row. It also makes field numbering easier to reason about because it starts at the first table column, even if the table begins on worksheet column C.

A normal worksheet AutoFilter range and a table are not identical features. A worksheet has one ordinary AutoFilter range, while separate Excel Tables can have their own filter controls. If a sheet contains several independent lists, tables are generally a better fit than repeatedly applying a standard filter to different blocks of cells.

Clear a Filter and Show All Records

There is an important distinction between removing filter criteria and removing the drop-down arrows. To show all records while keeping the controls available, call ShowAllData only when the worksheet is currently filtered. The check avoids an error when no filter is active. This procedure clears active filtering on the Data worksheet; it does not remove the filter arrows.

Sub ShowAllDataRows()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Show all rows only when a filter is hiding records.
    If ws.FilterMode Then ws.ShowAllData
End Sub

For a standard range, setting the worksheet’s AutoFilterMode property to False removes the filter arrows as well. Do not use that when the goal is merely to reveal hidden records and keep the controls. Also remember that manually hidden rows may remain hidden after a filter is cleared; AutoFilter and manual row hiding are separate operations.

Macros often need to work with the rows that remain visible after filtering. A visible-cells selection can include the header row if the selected range includes it, and it can fail when no data cells are visible in some selection patterns. Make the intended range explicit, account for the header, and handle the possibility of no matching records before copying or editing visible rows.

Common Problems and Checks

Wrong field number: Count from the first column of the filter range. Field 3 in A:D means C; field 3 in C:F means E.
Headers are missing or included as data: The filter range should start with the header row and include the records beneath it. Headers should be a single, clearly labeled row.
The range ends too soon: A fixed range such as A1:D100 will ignore later rows. Use an Excel Table or calculate the last row using a dependable key column.
The filter returns nothing: Check spelling, leading or trailing spaces, data types, and whether the criterion is being applied to the intended column. A number stored as text may not behave like a numeric value.
An AutoFilter error appears: Check that the range is valid and includes headers, that the worksheet and table names are correct, and that the code is not trying to apply an incompatible filter to a protected sheet.
Rows remain hidden after clearing: Check for manually hidden rows or another filter on the worksheet. A successful filter clear does not necessarily reset every form of row hiding.

For easier troubleshooting, first select a small known range and apply one simple criterion. Verify that the correct rows appear, then expand the code to dynamic ranges, multiple criteria, or copying visible results. This separates a field-numbering problem from a more complicated issue in the data or macro.

Windows, Mac, and Excel for the Web

The examples above target Excel for Microsoft 365 on Windows. The Visual Basic Editor opens with Alt+F11 on Windows; on Mac, the keyboard shortcut differs by keyboard and Excel settings, and the editor can also be opened from the Developer tab. Mac Excel supports VBA, but Microsoft documents limitations for the Range.AutoFilter method on Mac. If the macro must run on Mac, use a table-based approach supported by that Excel version or verify the exact AutoFilter call there before relying on it.

Excel for the web can display and use workbook filters, but it does not run VBA macros. A macro-enabled workbook can be opened in the web app, but running its VBA requires the desktop Excel application. Office Scripts are a separate automation option in supported Microsoft 365 environments; they use TypeScript rather than VBA, so VBA code cannot simply be pasted into an Office Script.

Frequently Asked Questions

How do I filter multiple columns with VBA?
Apply AutoFilter to the full range once, then call it for each field you want to constrain. The conditions on different fields work together, so a row must satisfy all active field filters to remain visible. Count every field from the left edge of the chosen range.

How do I remove only one column’s filter?
Reapply that field without criteria using an approach appropriate to the range or table, or use the filter drop-down to select all values. Avoid clearing the entire worksheet filter if other columns’ criteria need to remain. Test the behavior against the specific filter type used in the workbook.

Can AutoFilter find blank cells?
Yes. AutoFilter criteria can target blank or nonblank cells, but results depend on whether cells are truly empty or contain formulas returning an empty string. Those two cases may not be treated identically, so test with representative data before automating a blank-cell workflow.

Why does Field:=1 filter the wrong column?
Field numbering starts at the first column of the range supplied to AutoFilter, not at worksheet column A. If the filter range starts at D, field 1 is D. Check both the range’s left edge and the field number.

Can I undo a VBA AutoFilter with Ctrl+Z?
No. Excel’s undo history does not generally reverse changes made by a VBA macro. Save a copy before running code, and write a separate procedure to restore the desired filter state if that is important to your workflow.

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.