Excel VBA AutoFilter Syntax: Fields, Criteria, and Examples

VBA & macros · Updated November 21, 2026

In Excel VBA, call AutoFilter on a range that includes the list’s header row; the usual syntax uses the named arguments Field, Criteria1, Operator, and optionally Criteria2. Field is the column number counted from the left edge of that range, not the worksheet’s column number.

dataRange.AutoFilter Field:=1, Criteria1:="West"

That example shows only rows whose first field matches West. The range should include the column headers, and the field number is 1-based. The sections below explain how to define that range, choose criteria, and avoid common VBA filtering mistakes.

The AutoFilter method and its arguments

Range.AutoFilter applies a filter to a range, or changes the filter criteria for an existing one. Its arguments are optional, but named arguments make the intent easier to read. The common pattern is:

someRange.AutoFilter Field:=columnNumber, Criteria1:=firstCriterion, Operator:=filterOperator, Criteria2:=secondCriterion
  • Field identifies the field to filter, counted from the range’s first column.
  • Criteria1 supplies a value or comparison expression, such as "West", ">=100", or "*".
  • Operator specifies how criteria are combined or what filter type to use. Common choices include xlAnd, xlOr, and xlFilterValues.
  • Criteria2 provides a second criterion when the chosen operator or filter type needs one.
  • VisibleDropDown can show or hide the filter arrow for the specified field.

Not every filter needs every argument. A simple exact match generally needs only Field and Criteria1. Use named arguments rather than relying on argument position, especially when supplying an operator or a second criterion.

Choose the range and calculate Field correctly

Suppose a worksheet has headers in A1:D1, with Region in column A, Product in B, Status in C, and Sales in D. If the filter range is A1:D100, Region is field 1, Status is field 3, and Sales is field 4. If you instead filter B1:D100, Product becomes field 1 and Sales becomes field 3. The field number always starts at the range’s left edge.

A fixed address is suitable when the list size is known and stable. For a contiguous list that grows, CurrentRegion can find the block around a starting cell, provided blank rows and columns do not break it. For recurring data, an Excel Table is often a better foundation because it expands as rows are added.

The range should contain one header row and the rows to filter. Make sure the headers are present and distinct, and avoid including unrelated cells beside or below the list. A blank row or column inside the data can split a current region, while a selection that includes extra columns can make the Field numbers confusing.

Basic macro: filter a text value

Before running a macro, save a copy of the workbook. VBA changes made by a macro cannot be reversed with Ctrl+Z. The following example assumes a worksheet named Orders, headers in A1:D1, and records in rows 2 through 100. It filters the third field, Status, to show Open records.

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

    ' The range includes the header row.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1:D100")

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

Change the worksheet name, address, field number, and criterion to fit the workbook. The filter hides rows that do not match; it does not delete them. You can still access the hidden records after clearing the filter.

Text, numbers, blanks, and wildcard criteria

For text, use the value you want to match as Criteria1. AutoFilter criteria can also use comparison operators. For example, ">=100" selects numeric values at least 100, while "<>Closed" selects values other than Closed. The field’s underlying values matter: a number stored as text may not behave like a true numeric value.

For a numeric interval, combine two comparisons with xlAnd. This example filters the fourth field to values from 100 inclusive to 500 exclusive:

dataRange.AutoFilter Field:=4, Criteria1:=">=100", Operator:=xlAnd, Criteria2:="<500"

Wildcard criteria are useful for text. An asterisk represents any number of characters, and a question mark represents one character. Thus "*late*" matches text containing late, while "A?C" matches a three-character value beginning with A and ending with C. Use a tilde to escape a wildcard when you want to match the literal character rather than use it as a pattern.

To filter blank or nonblank cells, use Criteria1:="=" for blanks and Criteria1:="<>" for nonblank values in many ordinary ranges. Check the source data if results are surprising: a formula that returns an empty string is not always equivalent in filtering behavior to a genuinely empty cell.

Combine conditions and select several values

Use xlAnd when both conditions must be true for the same field, such as a sales value that is at least 100 and below 500. Use xlOr when either of two criteria can match. For instance, a text field can be filtered for either East or West with Criteria1:="East", Operator:=xlOr, Criteria2:="West".

To include a set of exact values in one field, pass an array and use xlFilterValues. This is useful for selecting several categories without building a chain of separate filters:

dataRange.AutoFilter Field:=1, Criteria1:=Array("East", "West"), Operator:=xlFilterValues

The array contains the values to keep in the first field. Matching is based on the actual cell values, so spelling, extra spaces, and inconsistent labels can affect which rows remain visible. For complex conditions spanning different columns—for example, Region is East and Status is Open—apply a filter to each field separately, using the same range and the relevant field number for each call.

Filter dates without locale surprises

Dates can be awkward because a date’s displayed format depends on regional settings, and cells may also contain times. One practical approach for true Excel date values is to compare their serial values. The following standalone macro filters the fourth field to dates in calendar year 2025, including any times on dates in that year but excluding dates from 2026 onward.

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

    ' Save a workbook copy before running a macro.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1:D100")

    ' Field 4 contains Excel date values.
    dataRange.AutoFilter Field:=4, _
        Criteria1:=">=" & CLng(DateSerial(2025, 1, 1)), _
        Operator:=xlAnd, _
        Criteria2:="<" & CLng(DateSerial(2026, 1, 1))
End Sub

This assumes the field contains real Excel dates, not text that merely looks like a date. The exclusive upper limit is the start of the following year, which handles date-time values on December 31 as well. Test date filters on a copy if the source column mixes text, blanks, and dates.

Use a Table or a growing data range

For data that changes size, format the list as an Excel Table using Insert > Table, or press Ctrl+T on Windows after selecting the list. Tables include filter arrows by default and expand when rows are added beneath them. In VBA, a table is represented by a ListObject. A table-based example looks like this:

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

    ' Save a copy before running a macro that changes filters.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set lo = ws.ListObjects("SalesTable")

    ' Field 1 is the first table column.
    lo.Range.AutoFilter Field:=1, Criteria1:="West"
End Sub

Replace SalesTable with the table’s actual name, available on the Table Design tab when the table is selected. For a non-table list that starts at A1 and has no blank row or column within it, ws.Range("A1").CurrentRegion can be assigned to a Range variable instead of typing a fixed last row. If the worksheet contains nearby data, a table or explicit address is safer than relying on CurrentRegion.

Clear filters and handle common problems

To show all rows again while keeping the filter arrows, check whether the worksheet is currently filtered and call ShowAllData. This standalone example uses a worksheet-level check:

Sub ClearWorksheetFilter()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Orders")

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

Calling AutoFilter without criteria can toggle filter arrows, so do not use it as a general-purpose “clear criteria” command unless toggling the arrows is what you intend. To clear one field’s filter while leaving other fields filtered, use that field’s dropdown and choose the clear-filter option, or write and test a targeted approach for the workbook’s filter setup.

When a macro appears not to work, check these common causes:

  • The field number was counted from worksheet column A instead of from the left edge of the filtered range.
  • The range omitted the header row or included unrelated cells.
  • The macro refers to the wrong worksheet, table name, or workbook.
  • Values contain trailing spaces, inconsistent spelling, or numbers stored as text.
  • The sheet is protected or another filter state is interfering with the intended change.

For troubleshooting, first confirm the range and field mapping, then try a simple exact-match filter on a small copy of the data. In Excel for Microsoft 365 on Windows, VBA macros run in the desktop application; Excel for the web can open a macro-enabled workbook but does not run VBA macros. Use the desktop app to execute and debug the code.

Frequently Asked Questions

What is the simplest Excel VBA AutoFilter syntax?
Use range.AutoFilter Field:=n, Criteria1:=value, where the range includes the headers and n is the field’s position within that range. Add Operator and Criteria2 when combining two conditions.

Does Field mean the worksheet column number?
No. Field is relative to the filtered range. If the range starts at column C, then column C is Field 1, column D is Field 2, and so on.

How do I filter for multiple values in one column?
Pass an array to Criteria1 and set Operator to xlFilterValues. For example, an array containing East and West keeps rows matching either listed value in that field.

How can I clear an AutoFilter but keep its arrows?
If the worksheet has an active filter, ws.ShowAllData displays all rows while leaving the dropdown arrows in place. Check ws.FilterMode first to avoid calling it when no filter is active.

Can Excel for the web run VBA AutoFilter macros?
No. Excel for the web can retain a macro-enabled workbook, but VBA code must be run in the Excel desktop application. Excel for Microsoft 365 on Windows is the assumed environment here; on Mac, verify the relevant AutoFilter method on the target Excel build because Microsoft documents platform differences for Range.AutoFilter.

Windows and Mac considerations

On Windows, the examples use the standard Excel VBA object model and named arguments. Windows Ctrl+T creates a table; on Mac, use the Table command on the ribbon or the equivalent shortcut supported by that Excel build. Microsoft’s VBA documentation notes that Range.AutoFilter is not supported on Excel for Mac and points to similar methods on Selection and ListObject. If the workbook must run on both platforms, test its filter code on the actual Mac version rather than assuming identical behavior. Excel for the web does not execute these macros.

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.