VBA AutoFilter Criteria1: Syntax, Examples & Tips in Excel

VBA & macros · Updated November 18, 2026

In VBA, Criteria1 is the first filter condition passed to Excel’s AutoFilter method; for example, Criteria1:="East" keeps rows whose selected field contains East. Its meaning depends on the field and, when supplied, the Operator and Criteria2 arguments.

What Criteria1 means in AutoFilter

AutoFilter applies a filter to a range or table, hiding rows that do not meet the supplied condition. Criteria1 is a Variant argument: it can be a text criterion, a comparison expression, or an array of choices. It is not the column name, and it does not identify the column to filter. The Field argument does that.

The general method signature is Range.AutoFilter(Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown). These arguments are optional in the method signature, but a useful filter normally specifies at least a field and a criterion. Named arguments make VBA easier to read because they show which value is being supplied:

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

    ' Make a copy of the workbook before running a macro.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Field 2 means the second column of dataRange.
    dataRange.AutoFilter Field:=2, Criteria1:="East"
End Sub

This example assumes a contiguous data block beginning at A1, with headers in row 1 and Region in the second column of that block. AutoFilter hides rows; it does not delete the records or rearrange their underlying values. A macro’s changes cannot be reversed with Ctrl+Z, so save a copy before running one.

How Field and the filter range work together

Field is a one-based position relative to the range on which AutoFilter is called. If the filtered range starts in column C, then Field:=1 means column C, not worksheet column A. If the range is A1:D100, Field 3 refers to column C within that range. This relative indexing is a frequent source of filters that target the wrong data.

Include the header row in the range so Excel can display filter arrows and interpret each field correctly. CurrentRegion is convenient when the data has no completely blank row or column splitting it from nearby content. If the sheet contains adjacent blocks or gaps, define the range explicitly instead, for example with a known last row or an Excel table. Check that the range covers every intended record; a filter cannot include rows outside it.

Text, exact matches, and comparison criteria

A plain text criterion usually selects matching cell values. For text filtering, Excel’s AutoFilter is generally not case-sensitive, so "east" and "East" do not normally distinguish capitalization. A criterion can also contain comparison operators. For instance, the strings ">1000", "<=500", and "<>Closed" mean greater than 1000, less than or equal to 500, and not equal to Closed.

For a numeric field, AutoFilter interprets comparison criteria against the cell values, not against formatted display text. A value displayed as $1,200 may be stored as the number 1200, so ">1000" can match it regardless of currency formatting. The following example filters a Sales column, assumed to be the third field in the range:

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

    ' Save a workbook copy first; macro changes cannot be undone with Ctrl+Z.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Keep records where the third field is greater than 1000.
    dataRange.AutoFilter Field:=3, Criteria1:=">1000"
End Sub

When a criterion is a text literal, pass it as a string. For dates, Excel stores dates as serial numbers even when cells display a calendar format. Date criteria expressed as text can be sensitive to regional date conventions. If a macro constructs a date comparison, build the date from numeric parts with DateSerial and consider how the target Excel locale interprets the criterion. Also confirm that the cells contain real Excel dates rather than text that merely looks like a date.

Filtering with two conditions

Use Operator:=xlAnd or Operator:=xlOr when one field needs two conditions. The two criteria apply to the same field: AND requires both conditions to be true, while OR accepts either condition. For example, to keep values greater than 100 and less than 500, use an AND condition; to keep either East or West, use OR:

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

    ' Save a copy before running this macro.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Field 2 matches either region.
    dataRange.AutoFilter Field:=2, Criteria1:="East", _
        Operator:=xlOr, Criteria2:="West"
End Sub

Do not confuse this with filtering two different columns. Calling AutoFilter once for Field 2 and again for Field 3 applies both filters to the range, so rows must satisfy both field-level filters. Within one field, Criteria1 and Criteria2 plus the operator express the pair of conditions.

Choosing multiple exact values with an array

When the goal is to keep any of several exact text or numeric choices in one field, use an array for Criteria1 and Operator:=xlFilterValues. This is often clearer than building a long chain of OR conditions. The following example keeps East, West, or North in field 2:

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

    ' Save a copy before running the macro.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1").CurrentRegion
    regions = Array("East", "West", "North")

    ' Show rows whose second field matches an array item.
    dataRange.AutoFilter Field:=2, Criteria1:=regions, _
        Operator:=xlFilterValues
End Sub

Array filtering is useful for a fixed set of allowed values, such as selected departments or product codes. It is not the same as a wildcard search: each array item represents a value to include, whereas wildcard criteria match patterns. If the list of values changes frequently, a macro can build the array from a worksheet range, but it should check that the source list contains values and that blanks are handled as intended.

Wildcards, blanks, and special cases

For text pattern matching, Excel criteria support * for any sequence of characters and ? for one character. For example, "*chair*" matches text containing chair, while "A?7" matches a three-character value beginning with A and ending in 7. Use a tilde to treat a wildcard character literally: "~*" searches for a literal asterisk, and "~~" represents a literal tilde.

For many ordinary AutoFilter fields, Criteria1:="=" selects blanks and Criteria1:="<>" selects nonblank cells. Check the source data when results look unexpected: formulas that return an empty string, cells containing spaces, and truly empty cells may not behave like the same kind of blank in every filtering context. Excel also provides special filter operators, such as xlTop10Items and xlBottom10Percent, for top or bottom value filters. Those operators change how the criteria are interpreted; consult the method’s documentation for the relevant operator and required criterion format.

Tables, existing filters, and clearing criteria

Excel tables have built-in filter controls, and VBA can apply a field filter to a table’s range. A table is often more reliable than CurrentRegion for expanding datasets because its defined range grows as rows are added. Remember that Field is still relative to the range being filtered; for a table it refers to the table’s column order, not necessarily the worksheet’s column letters.

Applying another AutoFilter criterion to a different field normally adds that field’s condition rather than replacing every existing filter. To reveal all rows while leaving the filter controls available, check whether the worksheet is in FilterMode before calling ShowAllData:

Sub ClearActiveFilters()
    Dim ws As Worksheet

    ' Save a copy before running a macro that changes worksheet state.
    Set ws = ThisWorkbook.Worksheets("Sales")

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

FilterMode indicates that a filter is currently hiding rows; AutoFilterMode indicates whether AutoFilter arrows are displayed. They are not interchangeable. Clearing active criteria does not necessarily remove the arrows. If you intend to remove the controls too, handle that separately and ensure you are targeting the correct range or table.

Common problems and platform notes

  • Wrong field: Recount from the first column of the range passed to AutoFilter; Field numbers start at 1.
  • Missing records: Confirm the selected range includes all rows and the header row, and that no blank row split a CurrentRegion.
  • Numbers treated unexpectedly: Check whether the cells contain numbers or numbers stored as text. Formatting a text value as a number does not convert its underlying type.
  • Criteria do not match: Look for leading or trailing spaces, spelling differences, formula-generated empty strings, and wildcard characters that need escaping.
  • “ShowAllData” error: Call it only when a filter is active; the FilterMode check in the example avoids calling it unnecessarily.

These examples target Microsoft Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros. On Mac, Microsoft’s VBA documentation notes that the Range.AutoFilter method is not supported in the same way as on Windows; it points to related Selection and ListObject approaches. If a macro must run on both platforms, verify the method against the Excel for Mac version and the actual range or table being filtered rather than assuming every Windows Range example is portable. The VBA editor and macro security settings also differ somewhat between platforms.

Frequently Asked Questions

What does Criteria1 mean in VBA AutoFilter?
Criteria1 is the first condition AutoFilter uses for the specified field. It can be a value such as "East", a comparison string such as ">1000", or an array when combined with xlFilterValues.

Does Field:=1 mean column A?
No. It means the first column of the range or table passed to AutoFilter. If the range starts in column C, Field 1 is column C.

How do I filter for either of two values?
For two alternatives in one field, provide Criteria1 and Criteria2 with Operator:=xlOr. For a longer list of exact choices, use an array with Operator:=xlFilterValues.

How do I filter for blank cells?
For a typical AutoFilter field, use Criteria1:="=" for blanks and Criteria1:="<>" for nonblank cells. Check whether apparent blanks are genuinely empty, contain spaces, or are formulas returning an empty string.

Can I undo an AutoFilter macro with Ctrl+Z?
No. Excel generally clears the undo history when a VBA macro runs. Save a copy of the workbook before running code that changes filters or other worksheet state.

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.