VBA AutoFilter Operator: Values, Uses, and Examples

VBA & macros · Updated November 18, 2026

In VBA, the Operator argument of Range.AutoFilter tells Excel how to combine two criteria for one field or which special filter mode to apply. Use xlAnd for records that meet both criteria, xlOr for records that meet either, and constants such as xlFilterValues for other filter types.

What the AutoFilter Operator Controls

AutoFilter displays only the rows that match conditions you specify. In VBA, the Operator argument is optional, but it becomes important when you give a field two criteria or want a filter mode other than an ordinary single-condition filter. It is not itself a comparison such as greater than or equal to: the criteria describe what to match, while the operator describes how to combine or interpret those criteria.

For example, if a Sales column should show values above 100 and below 500, the criteria are >100 and <500, and xlAnd combines them. A row must meet both conditions. If a Region column should show East or West, the criteria are the two region names and xlOr means either match is enough.

The operator applies to the criteria for one field in an AutoFilter call. Filters on different fields are generally cumulative: a row must pass the active filter on each field. That is why filtering Region to East and Sales to above 100 normally means “East and above 100,” even if the Sales field itself uses xlOr between two criteria.

AutoFilter Syntax and Field Numbers

The method’s commonly used arguments are Field, Criteria1, Operator, and Criteria2. Field identifies the column to filter, and the criteria specify the matching value or comparison. The field number is relative to the left edge of the range, not the worksheet’s column letter.

If the filter range is A1:D500, Field 1 is column A, Field 2 is B, Field 3 is C, and Field 4 is D. If the range instead starts at column C, Field 1 refers to C. The first row should normally contain headers so Excel can identify the filter fields. A typical call looks like this:

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

Here, field 3 is the third column of dataRange. Criteria can be text, numbers, dates, or comparison strings. Passing a single criterion usually does not require an operator. Named constants such as xlAnd make the intention clearer than numeric values.

Using xlAnd and xlOr

xlAnd and xlOr are the two operator constants most often needed for ordinary criteria on one field. They are members of Excel’s XlAutoFilterOperator enumeration. Their numeric values are 1 and 2 respectively, but VBA code should generally use the names rather than hard-coded numbers.

  • xlAnd: the row must satisfy both criteria. Example: a date is on or after January 1 and before February 1.
  • xlOr: the row may satisfy either criterion. Example: Status is Open or Pending.

For a numeric range, the criteria strings contain the comparison signs. For text alternatives, each criterion is a value to match. Conceptually, these calls mean:

  • Criteria1:=">100" with Criteria2:="<500" and xlAnd keeps values strictly between 100 and 500.
  • Criteria1:="East" with Criteria2:="West" and xlOr keeps rows matching either region.

“Strictly between” matters: greater than 100 excludes 100, and less than 500 excludes 500. To include an endpoint, use the appropriate comparison, such as >=100. Do not expect xlOr to create arbitrary nested logic across several columns; apply the conditions carefully and verify the resulting rows.

Other AutoFilter Operator Constants

The operator enumeration also supports filter modes beyond combining two ordinary criteria. These are useful when reproducing options from Excel’s filter menus in a macro. Common constants include:

  • xlTop10Items and xlBottom10Items for the highest or lowest number of items.
  • xlTop10Percent and xlBottom10Percent for the top or bottom percentage.
  • xlFilterValues for selecting multiple listed values, commonly by supplying an array in Criteria1.
  • xlFilterCellColor, xlFilterFontColor, and xlFilterIcon for filters based on cell color, font color, or an icon.
  • xlFilterDynamic for dynamic date or other dynamic filter choices supported by Excel.

For these modes, the criteria arguments and their meaning depend on the filter type. Do not assume the same pair of criteria used with xlAnd applies unchanged. For example, a multiple-value filter uses xlFilterValues and an array, rather than xlOr with a long series of criteria. Consult Excel’s VBA object documentation when implementing less common color, icon, or dynamic filters.

Practical VBA Examples

This Windows example assumes a worksheet named Data, headers in A1:D1, and records through row 500. The third column of the selected range is filtered to show values greater than 100 and less than 500. Make a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

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

    ' Use the worksheet and range that contain the table.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D500")

    ' Keep rows where the third field is between 100 and 500.
    dataRange.AutoFilter Field:=3, Criteria1:=">100", _
        Operator:=xlAnd, Criteria2:="<500"
End Sub

To select several exact values in one field, use xlFilterValues. With the same worksheet and range, this example filters the second field to Open or Pending:

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

    ' Use the worksheet and range that contain the table.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D500")

    ' Display rows whose second field is either listed value.
    dataRange.AutoFilter Field:=2, _
        Criteria1:=Array("Open", "Pending"), Operator:=xlFilterValues
End Sub

The code assumes those entries are actual values in the field and that the range includes the header row. If your data has a different size, adjust the range or determine it dynamically. A fixed range that stops too soon omits later records; one that includes unrelated cells can produce confusing filter menus.

Filtering Excel Tables and Choosing an Operator

If your data is formatted as an Excel Table, filtering is often easier to maintain than using a fixed address. Tables expand as rows are added and include filter buttons in their header row. In VBA, you can refer to the table by its ListObject name and apply a filter to its data range. This also makes it clearer which dataset a macro is meant to affect.

Choose the operator by asking what one field should match:

  1. For one comparison, such as Status equals Closed, provide one criterion and omit the operator unless the filter mode requires one.
  2. For two conditions that must both be true in the same field, use xlAnd.
  3. For either of two alternatives in the same field, use xlOr, or use xlFilterValues when selecting a list of exact values.
  4. For top or bottom rankings, colors, icons, or dynamic filters, choose the corresponding specialized constant.

Keep in mind that a field can only use the conditions available through AutoFilter’s arguments. If a requirement involves complicated combinations, such as several nested AND/OR groups across columns, a helper column or a different approach may be easier to understand and maintain.

Common Errors and Troubleshooting

A frequent mistake is using a worksheet column number as Field. If the range begins at C1, then Field 1—not Field 3—points to column C. Another common issue is including the wrong range: AutoFilter only evaluates the selected range, so confirm that it contains all headers and records and does not accidentally start below the header row.

Check the data type as well. A number stored as text may not behave like a numeric value when filtered with >100. Dates stored as text can also fail to sort or filter as expected. Inspect a few cells, make sure the values are consistent, and test the filter on a small known example before applying it to a large dataset.

When a macro runs without an obvious error but the rows look wrong, check the exact criteria, including comparison signs and spelling. For text matching, Excel supports wildcard characters in criteria: an asterisk represents any number of characters, a question mark represents one character, and a tilde can escape a wildcard when you need to match it literally. Also confirm whether an existing filter is active and whether the current filter range is the one your macro intends to change.

Clearing Filters and Platform Notes

Applying a new AutoFilter criterion updates the filter for that field; it does not necessarily clear filters on other fields. To show all records while retaining filter buttons, a macro can check whether the worksheet is in a filtered state before using ShowAllData. This avoids calling that method when there is no active filter, a situation that can otherwise cause a run-time error. Removing the arrows entirely is a different action from clearing the current criteria.

The examples above target Excel for Microsoft 365 on Windows. Excel for the web can display a workbook containing macros, but it cannot run VBA; use desktop Excel to execute the code. Microsoft’s VBA documentation notes that the Range.AutoFilter method is not supported on Excel for Mac, while related approaches through Selection or ListObject are supported. If a macro must run on Mac, adapt the object reference accordingly and verify the behavior in the Mac version being used.

Frequently Asked Questions

What is the default VBA AutoFilter operator?
If you omit Operator for a normal single criterion, Excel applies that criterion without needing an AND or OR combination. Supply an operator when combining two criteria or selecting a special filter mode.

What is the difference between xlAnd and xlOr?
xlAnd requires both criteria on that field to be true. xlOr accepts a row that meets either criterion. The comparison or text values themselves belong in Criteria1 and Criteria2.

How do I filter a column for multiple exact values?
Use Operator:=xlFilterValues and provide an array of values in Criteria1. This is generally more direct than trying to chain many pairs of criteria with xlOr.

Does Field mean the worksheet column number?
No. It is the field’s position within the range passed to AutoFilter. In a range beginning at A, Field 3 is column C; in a range beginning at C, Field 1 is column C.

Can I run VBA AutoFilter in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac, taking the Mac method support note into account.

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.