Excel VBA AutoFilter Operator Explained

VBA & macros · Updated November 21, 2026

In Excel VBA, the AutoFilter Operator argument tells Excel how to apply filter criteria: use xlAnd or xlOr to combine two conditions on one field, or use a specialized constant such as xlFilterValues or xlTop10Items for other filter types. The right operator depends on whether you are combining comparisons, choosing several values, or applying a preset filter.

What the AutoFilter operator means

Range.AutoFilter applies a filter to a range that has a header row. Its Operator argument is optional, but it matters when Excel needs to know how to interpret Criteria1 and Criteria2, or when you are applying a filter such as Top 10 or cell color. The operator does not identify the column; the Field argument does that.

For ordinary criteria, the two operators most people need are:

  • xlAnd: both criteria must be true. For example, a number must be greater than 100 and less than or equal to 500.
  • xlOr: either criterion can be true. For example, a region can be East or West.

These are members of Excel’s XlAutoFilterOperator enumeration. They are not text values: write xlAnd in VBA, not "xlAnd". Excel defines other members for value lists, color, icons, dynamic filters, and Top or Bottom filters.

AutoFilter syntax and the Field argument

The method’s general form is Range.AutoFilter(Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown). Arguments can be supplied by position, but named arguments make code easier to read and reduce mistakes. Field is the column number counted from the left edge of the filtered range, starting at 1. It is not necessarily the worksheet’s column number.

For example, if the filtered range is A1:C100, field 1 refers to worksheet column A, field 2 to B, and field 3 to C. If the range begins at column D, field 1 means D. The header row is part of the range, but it is not counted as a field. A range with headers in row 1 should usually include that row so Excel can show the filter dropdowns and associate each field with its heading.

Criteria1 and Criteria2 hold the conditions or values. Comparison criteria are generally strings such as ">100", "<=500", or "<>Closed". The operator joins or interprets those arguments; it does not replace them.

Use xlAnd for a range of values

Use xlAnd when a record must meet both conditions in the same field. A common example is keeping sales amounts above 100 and at most 500. Assume a worksheet named Sales has headers in row 1, amounts in column C, and the data range is A1:C100. This macro filters the third field to that interval:

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

    ' Set the worksheet and the complete range, including headers.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1:C100")

    ' Keep amounts greater than 100 and no more than 500.
    dataRange.AutoFilter Field:=3, Criteria1:=">100", _
        Operator:=xlAnd, Criteria2:="<=500"
End Sub

Because the conditions are on the same field, a value of 250 remains visible, while 100 and 500 are excluded or included according to the exact comparison signs: 100 is excluded by >100, and 500 is included by <=500. If the lower boundary should be included too, change the first criterion to >=100.

Before running a macro, save a copy of the workbook. Filtering changes which rows are visible, and macros cannot be undone with Ctrl+Z. This example assumes the range boundaries are correct; if rows are added over time, consider using an Excel Table so the data range can expand with the records.

Use xlOr for alternatives in one field

Use xlOr when either of two conditions on the same field is enough. For instance, to show rows whose Region field is East or West, set Criteria1:="East", Operator:=xlOr, and Criteria2:="West". If the data range starts in A1 and Region is its second column, the field is 2, even if the column has a different position in another worksheet layout.

There is an important limitation: xlAnd and xlOr combine two criteria for one field. They do not express a condition across two different columns. For example, “Region is East and Status is Open” needs filters set separately on the Region and Status fields; Excel applies filters on separate fields together. To express more complicated logic such as “Region is East and Status is Open, or Region is West and Status is Pending,” use a helper column, Advanced Filter criteria, or a formula-based approach rather than expecting one Operator value to describe the whole expression.

Text criteria can also use comparison operators and wildcards. In AutoFilter criteria, an asterisk matches a sequence of characters and a question mark matches one character; prefix a wildcard with a tilde when you need to search for the literal wildcard character. For example, a criterion beginning with "North*" can match text that starts with North. Wildcard matching is useful for text patterns, while xlAnd and xlOr determine how two supplied criteria relate.

Other XlAutoFilterOperator constants

The enumeration includes operators for filter types beyond simple AND/OR comparisons. The named constants are clearer and safer than entering their numeric values directly. Common members include:

  • xlTop10Items (3) and xlBottom10Items (4): show a specified number of high or low values.
  • xlTop10Percent (5) and xlBottom10Percent (6): show a specified percentage at the top or bottom.
  • xlFilterValues (7): apply a list of selected values, often passed through Criteria1 as an array.
  • xlFilterCellColor (8), xlFilterFontColor (9), and xlFilterIcon (10): filter by cell color, font color, or an icon.
  • xlFilterDynamic (11): apply a dynamic filter category, such as a date-related choice supported by Excel’s filter interface.

For example, a value-list filter uses xlFilterValues because the criterion is a set of choices rather than two conditions joined by AND or OR. A Top 10 filter uses its matching Top or Bottom constant. The criteria required for these special filters depend on the filter type, so do not substitute xlAnd merely because the call has a Criteria1 argument. When possible, record a short macro from the filter dropdown in the target Excel version and inspect the resulting arguments as a starting point.

Common mistakes and how to troubleshoot

Most AutoFilter problems come from a mismatch between the range, field number, and criteria. Check these points before changing the operator:

  • Field counted from the wrong place: Recount from the left edge of the range passed to AutoFilter, not from worksheet column A.
  • Header row omitted: Include the headers in the filter range. A data-only range may produce unexpected results or missing dropdowns.
  • Criteria joined incorrectly: If a value must satisfy both boundaries, use xlAnd. If either of two values is acceptable, use xlOr.
  • Numbers stored as text: A column containing a mix of numeric values and text-looking numbers may not filter as expected. Check the underlying cell values and make the column consistent.
  • Existing filters or a different range: A worksheet may already have an AutoFilter or a table filter. Confirm the macro targets the intended range and field rather than assuming the active selection is the data.
  • Criteria do not match the data: Look for extra spaces, different spelling, date formats, and inclusive versus exclusive comparison signs.

Filtering hides nonmatching rows; it does not delete them. If a macro runs without an obvious error but some records disappear from view, clear the filter to check whether the rows are merely hidden by the criteria. For a standard range, Excel’s Data tab includes filter controls; for an Excel Table, dropdowns belong to the table’s header row.

Range.AutoFilter on Windows, Mac, and the web

This article’s VBA examples target Excel for Microsoft 365 on Windows. Microsoft’s VBA reference notes that the Range.AutoFilter method is not supported in Excel for Mac, although related AutoFilter methods on Selection and ListObject are supported. Mac users should check the method and object used by their specific workbook rather than assuming that a Windows Range.AutoFilter example will run unchanged. Excel for the web can display and use worksheet filters, but it does not run VBA macros; run VBA in a desktop version of Excel.

For a task that does not need VBA, the worksheet interface may be simpler: select a cell in the data and use the Filter command on the Data tab, then choose the dropdown options. The interface is also a practical way to confirm the intended criteria before automating them. If you need a repeatable macro, verify the object type—ordinary range or table—and the platform before building the procedure around a particular method.

Frequently Asked Questions

What is the difference between xlAnd and xlOr in VBA AutoFilter?
xlAnd requires both supplied criteria to be true for a field. xlOr keeps a record when either criterion is true. They combine criteria for one field, not conditions across multiple columns.

Can I use more than two criteria with AutoFilter?
For ordinary comparison criteria, the method exposes Criteria1 and Criteria2. To select multiple discrete values, use an array with xlFilterValues. For more complex logic, use multiple field filters, a helper column, or Advanced Filter rather than trying to extend xlAnd or xlOr beyond their role.

Does Field refer to the worksheet column number?
No. It is the position of the column within the filtered range, starting at 1. If the range begins at D1, field 1 is column D, and field 2 is column E.

Do I need to specify Operator every time?
No. It is optional. Specify it when combining two criteria with AND or OR, or when applying a specialized filter type. A basic single criterion can often be applied without an operator.

Why does xlAnd not filter two different columns?
The operator combines criteria for one AutoFilter field. To filter two columns, apply criteria to each field separately; Excel then shows rows that meet the active filters on both fields. For mixed multi-column OR logic, use a helper field or another filtering method.

Choosing the right operator

Start by deciding what the filter means in plain language. If a record must meet two comparisons on one column, use xlAnd; if either of two alternatives is acceptable, use xlOr. If the task is selecting several listed values or filtering by top values, color, an icon, or a dynamic category, choose the corresponding specialized constant. Then verify the range includes its header, count the field relative to that range, and check the criteria against real cell contents. That small checklist prevents most operator-related errors.

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.