Excel VBA AutoFilter: Exclude Multiple Values

VBA & macros · Updated November 21, 2026

Excel VBA AutoFilter cannot use an array of “not equal to” criteria as a general exclusion list. An array passed with Operator:=xlFilterValues is an inclusion list; to exclude two values, use two criteria joined with xlAnd, and for more exclusions use a helper column or build a list of values to include.

Why the array behaves differently

AutoFilter’s array syntax is useful when a column should show several chosen values. For example, if a status field contains Open, Pending, and Closed, a filter array containing Open and Pending means “show rows whose status is Open or Pending.” The list describes acceptable values; it does not describe values to remove.

This difference matters because ordinary filter criteria and value-list criteria are separate modes. A single criterion such as <>Closed means “not equal to Closed.” But placing strings such as <>Closed and <>Pending into an array and specifying xlFilterValues does not reliably turn those strings into a series of exclusion rules. Excel treats that array as selected values, not as multiple logical comparisons.

That is why a line that looks plausible can return no rows or unexpected results. The fix is to choose the filter structure that matches the logic: AND together a small number of exclusions, or calculate an explicit TRUE/FALSE result for a longer exclusion list.

How AutoFilter criteria work

The Range.AutoFilter method applies a filter to a range that has a header row. Its Field argument is the column number counted from the left edge of that filtered range, not the worksheet’s absolute column number. If the filtered range is A1:D100, Field 2 refers to column B. This is a frequent source of filters being applied to the wrong field.

For a normal criterion, Criteria1 can hold a comparison such as <>Closed, >100, or a text value. To combine two comparisons, set Operator to xlAnd or xlOr and supply Criteria2. “Not Closed and not Pending” needs AND: a row must satisfy both comparisons to remain visible.

By contrast, Operator:=xlFilterValues is the mode commonly used with an array of discrete values. It is appropriate for “show A or B or C,” not “show everything except A, B, and C.” The distinction is about the meaning of the operator, not a limitation in VBA’s ability to create arrays.

Exclude two values with xlAnd

For exactly two exclusions, use two not-equal criteria connected by AND. In this example, the data has headers in row 1 and runs from A1:D100; column B contains statuses. The code filters out Closed and Pending while leaving other status values eligible to appear.

Sub ExcludeTwoStatuses()
    Dim ws As Worksheet
    Dim filterRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set filterRange = ws.Range("A1:D100")

    ' Field 2 is column B within the A:D filter range.
    filterRange.AutoFilter Field:=2, Criteria1:="<>Closed", _
        Operator:=xlAnd, Criteria2:="<>Pending"
End Sub

Because a row cannot have a value equal to both Closed and Pending at once, the AND combination removes either excluded status: Closed fails the first test and Pending fails the second. The code applies the filter; it does not delete rows. Rows that do not meet the criteria are hidden until the filter is cleared or changed.

Save a copy of the workbook before running a macro. Although this example only filters rows, VBA macros can make changes that Ctrl+Z cannot undo, and other code may have more lasting effects.

Exclude more than two values

AutoFilter’s ordinary custom-filter interface is designed around up to two criteria for a field. For a longer, changeable exclusion list, a helper column is usually clearer and easier to maintain than trying to pass “not equal” strings in an array. Put excluded statuses in F2:F4, for example, and use a helper formula beside each data row. With the status in B2 and exclusions in F2:F4, enter this in a helper cell on row 2 and fill it down:

=COUNTIF($F$2:$F$4,B2)=0

The formula returns TRUE when B2 is absent from the exclusion list and FALSE when it matches one of its entries. Filter the helper column to TRUE. If the three excluded values are Closed, Pending, and Cancelled, a row with Open in B2 returns TRUE; a row with Pending returns FALSE. Updating the list in F2:F4 changes the result without editing the macro’s criteria.

If blank statuses need special treatment, decide that explicitly. With the formula above, a blank B2 will generally pass when the exclusion list contains no blank entry. To exclude blanks as well as the listed statuses, use this formula in the same layout and fill down:

=AND(B2<>"",COUNTIF($F$2:$F$4,B2)=0)

Filter that helper column to TRUE. COUNTIF is not case-sensitive and treats asterisks and question marks in criteria as wildcard characters. If the exclusion values may contain those symbols as literal text, account for Excel’s wildcard rules rather than assuming the formula is a character-for-character comparison.

When an inclusion array is the better choice

Sometimes the goal is not to exclude a few items but to show a known set of allowed items. In that case, use the array approach directly. For example, if column B should show only Open and In Progress, the relevant call is:

filterRange.AutoFilter Field:=2, _
    Criteria1:=Array("Open", "In Progress"), _
    Operator:=xlFilterValues

This is an OR-style selection: a row is retained if its field matches one of the array values. It can be a practical alternative when the allowed list is short and explicit, or when you can generate the allowed values from a known set. It is less convenient when the data contains many distinct values and the excluded list is what changes regularly.

For a finite set of possible statuses, you can build an allowed array by taking all statuses except the excluded ones, then pass that array with xlFilterValues. The array must contain the values to keep, not the values to reject. Also check the case where the resulting allowed list is empty; calling AutoFilter with an empty or unsuitable array may not produce the intended “show no rows” behavior. A helper column handles that edge case more explicitly.

Common problems and checks

  • Wrong Field number: count from the first column in the filter range. For a range beginning in C, Field 1 means C, not A.
  • Using xlFilterValues with operators: use it for selecting values, not as a way to combine many <> comparisons.
  • Using OR for exclusions: “not A or not B” is true for almost every row, including rows equal to A or B. Use AND so a row must be different from both.
  • Unexpected blanks: test blank cells separately and decide whether they should remain visible. A helper formula makes that rule easy to state.
  • Headers or range boundaries: the first row of the AutoFilter range should contain field names, and the range should include all rows that need filtering.
  • Existing filter state: AutoFilter changes the criteria on the selected field, while filters on other fields may still be active. Clear or inspect other field filters if the visible result seems too restrictive.

Also check whether the values include trailing spaces or inconsistent spellings, such as “Pending” and “Pending ”. Those are different text values for matching purposes. A helper column can normalize data if needed, but avoid changing source values without understanding how other formulas, reports, or macros depend on them.

Windows, Mac, and workbook considerations

The code example targets Excel for Microsoft 365 on Windows. The AutoFilter feature is available through Excel’s interface on both Windows and Mac, but VBA support for particular objects and methods differs. Microsoft’s VBA reference notes that the Range.AutoFilter method is not supported on Excel for Mac and points to Selection and ListObject alternatives. Mac users who need a macro should use a table-based approach or verify the supported method for their Excel version rather than assuming the Windows Range call will work unchanged.

If the source data is an Excel table, a table’s filter buttons and structured data range can make the workbook easier to maintain as new rows are added. On Windows, you can use the table’s range in VBA and apply criteria to its fields. Whatever approach you choose, test it on a copy and confirm that adding rows, clearing filters, and changing the exclusion list behave as expected.

Frequently Asked Questions

Can I use Criteria1:=Array("<>A", "<>B")?
No, not as a dependable list of not-equal tests. With xlFilterValues, an array represents values to include. Use Criteria1 and Criteria2 with xlAnd for two exclusions, or use a helper column for more.

Why does AutoFilter need xlAnd for two exclusions?
A row must be different from A and different from B to exclude both. With OR, a row equal to A is still different from B, so it can pass the combined test. AND expresses the intended logic.

Can AutoFilter exclude three or more values directly?
Not as a general array of “not equal” conditions. A helper column that checks a cell against an exclusion list is usually the most maintainable option. Alternatively, construct the array of all values to keep and use xlFilterValues.

Does the macro delete the excluded rows?
No. AutoFilter hides rows that do not meet the criteria; the underlying data remains in the worksheet. Clearing the filter makes the rows visible again.

How do I filter a table instead of a normal range?
Use the table’s data range and identify the field relative to that range. A table is often preferable when rows are added over time because its data area expands with the table. On Mac, use a supported Selection or ListObject VBA method rather than assuming Range.AutoFilter is available.

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.