VBA Range AutoFilter: Examples, Criteria, and Fixes

In Excel VBA, use Range.AutoFilter to filter a range that includes its header row; set Field to the column’s position within that range and supply a condition with Criteria1. For example, dataRange.AutoFilter Field:=3, Criteria1:=">1000" shows records whose third column is greater than 1,000.

What Range.AutoFilter does

AutoFilter adds filter drop-downs to a header row and hides rows that do not meet the selected conditions. It does not delete records, move them to another sheet, or change the values in filtered-out rows. The range remains in place, so code that processes visible cells may need to account for hidden rows separately.

The method is useful for repeatable tasks such as showing overdue orders, narrowing a sales list to one region, or displaying only records above a threshold. It uses Excel’s built-in filtering behavior, so users can still adjust the drop-downs after the macro runs. A filter is not the same thing as sorting: filtering hides nonmatching rows, while sorting changes row order.

A basic VBA AutoFilter example

Suppose a worksheet named Sales has headers in A1:C1 and records in A2:C100, with the sales amount in column C. The following procedure filters amounts greater than 1,000. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

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

    ' Include the header row and all three columns.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1:C100")

    dataRange.AutoFilter Field:=3, Criteria1:=">1000"
End Sub

Run the macro from the Visual Basic Editor or assign it to a button. If the range already has filter arrows, applying a new criterion to the same field updates that field’s filter. The method does not automatically expand the fixed A1:C100 address when new records are added, so choose a range strategy that matches how the data grows.

How Field and the filter range work

Field is a one-based position counted from the left edge of the range passed to AutoFilter, not necessarily the worksheet’s column number. If the range is A1:C100, Field 1 means worksheet column A, Field 2 means B, and Field 3 means C. If the range instead begins in column D, Field 1 means D.

The range should include one header row and the data columns to be filtered. For a range A1:C100, the header row is row 1, and Field 3 refers to the third included column. A common mistake is to pass only the data rows: Excel then treats the first record as the header, and the drop-downs or results can be wrong. Avoid including unrelated columns unless you intend the filter range to cover them too.

Use a contiguous range with a distinct header for each column. Blank rows inside a list can interfere with automatic range detection, while blank cells within a data column are usually valid records. When the exact final row is known, an explicit address is predictable. When it changes, an Excel Table or a carefully selected dynamic range is generally easier to maintain.

Common criteria and operators

Criteria are usually strings, even when they represent numbers. Comparison operators such as >, >=, <, <=, and <> can be combined with a value. For example, Criteria1:="<>Closed" excludes records whose cell value is Closed. For a text match, use a criterion such as Criteria1:="East"; wildcard characters can help when matching text patterns.

Use an asterisk (*) to represent any number of characters, such as "North*" for values beginning with North.
Use a question mark (?) for one character, such as "A?" for two-character values beginning with A.
Use a tilde (~) before a wildcard when you need to match that character literally.
Use "=" to filter for blank cells and "<>" to filter for nonblank cells, subject to how the column’s formulas and values are stored.

For example, with the same Sales layout, Criteria1:=">=500" keeps values of 500 or more. A criterion is interpreted according to the cell values and Excel’s filter rules; text that looks like a number may not behave like a numeric value. Check the source column’s data type if a comparison unexpectedly returns no rows.

Filtering with multiple conditions

To apply conditions to different columns, call AutoFilter for each field on the same range. Each call sets that field’s criterion, while filters on other fields remain in effect. For example, first filter the region column and then the sales column; the visible rows must meet both field conditions.

For two conditions on the same field, use the Operator argument. With names in A and amounts in C in the A1:C100 range, this call keeps amounts greater than 500 and less than 2,000:

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

    ' Both conditions apply to the amount field.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1:C100")

    dataRange.AutoFilter Field:=3, _
        Criteria1:=">500", Operator:=xlAnd, Criteria2:="<2000"
End Sub

Replace xlAnd with xlOr when either of the two conditions should qualify a row. To select several discrete text values, the filter can use an array with Operator:=xlFilterValues. The values should match the entries as they appear in the column:

Sub FilterTwoRegions()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim regions As Variant

    ' The region field is the second column in A1:C100.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1:C100")
    regions = Array("East", "West")

    dataRange.AutoFilter Field:=2, _
        Criteria1:=regions, Operator:=xlFilterValues
End Sub

Dates, blanks, and data types

Date filters deserve extra care because a date displayed as 1/2/2025 can be interpreted differently under different regional settings, and a cell containing text that looks like a date is not necessarily an Excel date value. Prefer storing real Excel dates consistently in the source column. If a macro builds date criteria from text, verify the date format and regional settings on the computers where the workbook will run.

For a date interval, a practical approach is to use the filter drop-down’s date hierarchy or create criteria from known date values and test the result on representative records. Decide whether the end date is inclusive. For example, an inclusive end-of-month condition can behave differently if the column contains times as well as dates; filtering before the first day of the next month is often a clearer boundary. Blank and nonblank criteria also depend on whether cells are truly empty or contain formulas returning an empty string.

When a filter seems to miss a record, inspect the underlying cell rather than its display alone. Numbers stored as text, leading or trailing spaces, inconsistent date values, and formulas returning empty text can all produce results that differ from a person’s visual expectation.

Choosing a range that grows with the data

A fixed address such as A1:C100 is simple but excludes row 101 and beyond. One alternative is to use a worksheet’s CurrentRegion, which selects the contiguous block around a starting cell. It is convenient for a solid table of data, but a fully blank row or column can break the block, and adjacent notes may be included if they touch it.

An Excel Table is often more dependable for lists that grow. Tables expand as users add records and provide a named object that VBA can reference. On Windows, a table’s range can be filtered by field position just like another range:

Sub FilterOrdersTable()
    Dim ws As Worksheet
    Dim orders As ListObject

    ' The table name is set in Excel's Table Design tab.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set orders = ws.ListObjects("Table1")

    orders.Range.AutoFilter Field:=3, Criteria1:=">1000"
End Sub

Here, Field 3 is the third column of Table1, not necessarily worksheet column C. Replace Table1 with the actual table name. If the table has no data rows, test that case before running code that assumes records exist. Tables also make the range’s purpose clearer to future maintainers than a hard-coded last-row number.

Removing filters and showing all records

There are two different cleanup actions: clear the active criteria so all records show, or remove the filter arrows. ShowAllData restores rows hidden by filtering while leaving the AutoFilter interface in place. Setting AutoFilterMode to False removes the arrows; it should not be confused with turning off a filter criterion alone.

Sub ClearSalesFilters()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sales")

    ' Clear criteria only when a filter is active.
    If ws.FilterMode Then ws.ShowAllData

    ' Remove the AutoFilter arrows if they are displayed.
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
End Sub

Use only the action you need. If users should continue filtering manually, clear the criteria but keep the arrows. If a worksheet contains a table, removing worksheet AutoFilter arrows may not be the right cleanup for that table; manage the table’s own filter state and test the result in the workbook.

Common problems and practical checks

Wrong field number: Count columns from the left edge of the filtered range, starting at 1.
Header missing or included incorrectly: Include exactly the header row and the records, and confirm the first row contains labels rather than data.
New records are not filtered: Expand the range or use a Table so additions are included.
No rows appear: Check whether the comparison matches the actual values, whether numbers are stored as text, and whether another field has a restrictive filter.
ShowAllData causes an error: It can fail if no filter is currently hiding rows. Check FilterMode before calling it, as in the cleanup example.
Rows appear missing after the macro: AutoFilter hides rows; it does not erase them. Clear the filter to show all records, and check for manually hidden rows as well.

Also check worksheet protection, merged cells in the list, and the exact range address if the filter interface behaves unexpectedly. A quick diagnostic is to inspect the header row and test one criterion manually using Excel’s drop-down. That helps distinguish a data problem from a VBA range or criteria problem.

Windows, Mac, and macro safety

The examples here target Excel for Microsoft 365 on Windows. Microsoft documents that the Range.AutoFilter method is not supported on Excel for Mac; similar filtering interfaces exist for Selection and ListObject, but compatibility should be checked in the specific Mac version and workbook setup. If a workbook must run on both platforms, test the filtering code on each, or use Excel’s interface and a platform-appropriate implementation rather than assuming the Windows call will behave identically.

Macros must be stored in a macro-enabled workbook, typically an .xlsm file, and Excel’s macro security settings determine whether they can run. Only enable macros in files from sources you trust. Before running a procedure that hides rows or changes filter state, save a separate copy: Ctrl+Z cannot undo VBA actions. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, the editor shortcut and available VBA support can vary by keyboard and Excel version, so use the Visual Basic Editor command from Excel’s Developer tools if the shortcut differs.

Frequently Asked Questions

Does Range.AutoFilter delete rows?
No. It hides rows that fail the criteria and leaves the underlying data in the worksheet. Clearing the filter makes those rows visible again unless they were hidden separately.

Is Field the worksheet column number?
Not necessarily. Field is the one-based column position within the range supplied to AutoFilter. In a range starting at D1, Field 1 refers to column D.

How do I filter for more than one value?
For two conditions on one field, use Criteria1, Criteria2, and an operator such as xlOr. For a list of discrete values, pass an array as Criteria1 and use xlFilterValues.

How do I remove a filter without deleting data?
Use ShowAllData when a filter is active to show all records while retaining the arrows. Set AutoFilterMode to False when you also want to remove the worksheet’s AutoFilter arrows.

Why does my filter omit new rows?
The macro may be filtering a fixed range that ends before the new records. Extend the address, calculate the last row carefully, or use an Excel Table whose range expands with added records.

Does this VBA code work in Excel for Mac?
Do not assume that Range.AutoFilter will work on Mac; Microsoft’s documentation identifies that method as unsupported there. Check the target Mac version and use a supported Selection or ListObject filtering approach if cross-platform execution is required.

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.