VBA AutoFilter Field Criteria1: Values, Operators & Examples

VBA & macros · Updated November 18, 2026

In Excel VBA, Field identifies which column within the filtered range to filter, while Criteria1 supplies the first condition to match. For example, Range("A1:D100").AutoFilter Field:=2, Criteria1:="East" filters the second column of that range to show rows whose value is East.

The method is useful for filtering a list or table without manually opening its dropdown. The important detail is that Field:=2 means the second column of the range passed to AutoFilter—not necessarily worksheet column B. Understanding that, along with the criteria syntax and optional operator, prevents many common VBA filtering errors.

What Field and Criteria1 mean

AutoFilter applies dropdown filters to a range with a header row. Its Field argument is a one-based position counted from the left edge of that range. Criteria1 is a Variant argument that specifies the value or condition for that field. In everyday use, it is usually a string, such as a category name or a comparison expression.

Suppose the range is A1:D100 and the headers are Date, Region, Product, and Sales. Field 1 refers to Date, Field 2 to Region, Field 3 to Product, and Field 4 to Sales. If the range instead begins at column C, then Field 1 refers to worksheet column C. The field number does not change just because the range begins in a different worksheet column.

The range should include the header row and the records to be filtered. Excel uses that first row to label the filter dropdowns. If the range accidentally excludes a header or includes unrelated columns, the field positions and results may not be what the code expects.

A basic VBA example

This example filters the Region column in a worksheet range. Before running a macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z. The workbook must also be saved in a macro-enabled format, such as .xlsm, if you want to retain the macro.

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

    ' The first row contains headers; Region is the second field.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1:D100")
    dataRange.AutoFilter Field:=2, Criteria1:="East"
End Sub

In this example, the method shows matching rows and hides the other data rows; it does not delete them. The worksheet name and range must exist in the workbook. If the list grows beyond row 100, the fixed range will not include newer records, so a table or a dynamically determined range may be more suitable.

On Windows, open the Visual Basic Editor with Alt+F11. On Mac, Option+F11 is the usual shortcut; some keyboards require Fn as well. Excel for the web can display a workbook containing VBA, but it cannot run VBA macros. Macros are available in the desktop Excel applications, subject to the workbook’s security settings.

Writing text, numeric, and wildcard criteria

For a text category, set Criteria1 to the text to match, for example Criteria1:="East". A numeric condition is commonly written as a comparison string. The comparison operators are >, >=, <, <=, and <> (not equal). For example, a Sales field can be filtered to values greater than 500 with Criteria1:=">500". The criteria is a string even though the cells contain numbers.

Text criteria can use wildcard characters. An asterisk represents any sequence of characters; a question mark represents one character. For example, Criteria1:="North*" finds text beginning with North, while Criteria1:="*east*" finds text containing east. To match a literal wildcard rather than use it as a pattern, precede it with a tilde: ~* represents a literal asterisk.

  • Use Criteria1:="<>Closed" to exclude a text value such as Closed.
  • Use Criteria1:="=" to show blank cells, or Criteria1:="<>" to show nonblank cells.
  • Check spelling and spaces in source values. A value such as East followed by a space may not match the intended text criterion.

Excel’s filter criteria are not ordinary VBA expressions. For instance, writing Criteria1:=>500 is invalid VBA; the operator and number belong inside a quoted string. Also, use straight quotation marks in code, not typographic “smart quotes.”

Combining conditions with Operator and Criteria2

Criteria1 is the first condition, but it is not the only available condition. The AutoFilter method also accepts Criteria2 and Operator. For a numeric range, xlAnd means both conditions must be true; xlOr means either condition can be true.

For example, to retain sales greater than 100 and less than 500, apply two conditions to the same field:

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

    ' Sales is the fourth column in A1:D100.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1:D100")
    dataRange.AutoFilter Field:=4, Criteria1:=">100", _
        Operator:=xlAnd, Criteria2:="<500"
End Sub

The result includes values strictly between 100 and 500; values equal to either boundary are excluded. Change the strings to >=100 and <=500 if the boundaries should be included. For two alternative conditions, such as values below 100 or above 500, use Operator:=xlOr. The operator joins the two conditions for one field; it does not combine filters across separate columns.

Filtering for multiple listed values

When the goal is to show several specific text values, use an array for Criteria1 together with xlFilterValues. This is different from using xlOr with two criteria: the array can contain a longer set of selected values.

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

    ' Region is the second field in this range.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1:D100")
    dataRange.AutoFilter Field:=2, _
        Criteria1:=Array("East", "West"), Operator:=xlFilterValues
End Sub

This keeps rows where Region is East or West, assuming those are the exact entries in the source data. The array values must be supplied as an array, not as one comma-separated text string. If categories come from cells or a variable-length list, build a Variant array and pass that array to Criteria1. For date filters, Excel may represent periods using grouped date criteria, so a simple list of formatted date strings is not always equivalent to selecting dates from the filter menu.

Using AutoFilter with an Excel table

Excel tables, created with Insert > Table, expand when records are added and are generally more reliable than a hard-coded range. A table is represented in VBA by a ListObject. Its Range includes the table headings and data, so the field index is still relative to the table’s leftmost column.

For example, if a table named SalesTable has a Region column, lo.ListColumns("Region").Index returns the position of that column within the table. Using the column name to obtain the field number is often clearer than embedding a number such as 2, especially if someone may rearrange the table columns.

Sub FilterSalesTable()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim regionField As Long

    ' The table name is set in Table Design > Table Name.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set lo = ws.ListObjects("SalesTable")
    regionField = lo.ListColumns("Region").Index
    lo.Range.AutoFilter Field:=regionField, Criteria1:="East"
End Sub

If the worksheet or table name is wrong, VBA raises an error before filtering. A table’s filter arrows may already be visible; applying the criteria changes which table rows are displayed. The table remains intact, and new rows added to it are included in the table range.

Clearing filters and avoiding common errors

Applying another criterion to a field replaces that field’s current criterion. Filters applied to other fields can remain active, so the visible rows may reflect several conditions at once. To clear all active worksheet filters while leaving the filter controls in place, check whether the sheet is filtered before calling ShowAllData:

Sub ClearWorksheetFilters()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sales")
    ' ShowAllData errors if there is no active filter to clear.
    If ws.FilterMode Then ws.ShowAllData
End Sub

This clears the active filtering on that worksheet; it is not the same as deleting records or necessarily removing the dropdown arrows. If you want to clear only a particular field, reapply the filter to that field with the desired criterion rather than clearing every condition on the sheet.

  • Wrong field: Recount from the leftmost column of the supplied range, not from column A.
  • Missing results: Confirm the range includes all records and the header row, and check for extra spaces, different spelling, or data stored in an unexpected format.
  • Type mismatch: A number stored as text may behave differently from a numeric value. Inspect the source cells before changing the criteria.
  • Unexpected combined result: A filter on another column may still be active. Clear filters or inspect each field’s dropdown.
  • Error when clearing: Call ShowAllData only when the worksheet is actually filtered, as in the guarded example above.

AutoFilter hides rows rather than removing them. That matters if later code loops through a range: ordinary loops can still encounter hidden rows unless the code explicitly works with visible cells. Filtering is a display and selection operation, not a permanent data-cleaning step.

Choosing between AutoFilter and other approaches

Use AutoFilter when users should see a filtered list in place and may want to change the dropdown selections afterward. It suits interactive reports, compact data review, and macros that temporarily narrow a table. It also preserves the original rows, which makes it safer than deleting records just to focus on a subset.

For a repeatable extraction into a separate output area, a VBA loop that checks each row may offer more control over copying, transformation, and logging. In Microsoft 365, worksheet functions such as FILTER can return matching data as a dynamic array, but that is a worksheet-formula alternative rather than a VBA filter operation. Use a formula when a live result that updates with source data is desired; use AutoFilter when changing the visible state of a list is the goal. Excel for the web supports many worksheet functions but does not execute VBA.

Frequently Asked Questions

Does Field:=2 mean column B?
No. It means the second column in the range passed to AutoFilter. If the range starts in column C, Field 2 is worksheet column D.

Can Criteria1 contain more than one value?
Yes. Pass a Variant array such as Array("East", "West") and use Operator:=xlFilterValues to show multiple selected values in one field.

How do I filter for values greater than a number?
Put the comparison operator and number in a quoted string, such as Criteria1:=">500". Use >= instead of > when the boundary value should also appear.

Why does AutoFilter return no matching rows?
Check the field index relative to the range, the range boundaries, and the exact contents and data types of the cells. Extra spaces, values stored as text, or another active field filter can change the result.

Can I run this VBA in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks but cannot run VBA macros. Run the code in desktop Excel for Windows or Mac instead.

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.