VBA AutoFilter Criteria Not Equal: How to Use It

VBA & macros · Updated November 18, 2026

To filter out a value with VBA AutoFilter, pass a not-equal criterion such as "<>Closed" to the field you want to filter: dataRange.AutoFilter Field:=2, Criteria1:="<>Closed". The field number is the column’s position inside the filtered range, not necessarily its worksheet column number.

What “not equal” means in AutoFilter

Excel’s AutoFilter uses criteria strings for comparisons. The operator <> means “not equal to,” so "<>Closed" asks Excel to show rows whose value in the chosen field is not Closed. This is useful for excluding a status, category, name, or numeric value while leaving the rest of the list visible.

The criterion is passed as the Criteria1 argument of the Range.AutoFilter method. A basic call has this shape:

someRange.AutoFilter Field:=fieldNumber, Criteria1:="<>value"

Here, someRange should include the header row and the records to filter. Field identifies a column within that range, counting from left to right starting at 1. For example, if the range begins in column D, its first field is still 1, even though the worksheet column number is 4.

Text comparisons in Excel filters are generally case-insensitive: a criterion excluding Closed will also exclude CLOSED. The criterion should match the underlying cell value, not just the way a value appears because of cell formatting. A number formatted to display as currency is still a number, and a date is stored as a date serial value.

Set up the range and field number correctly

AutoFilter works best with a rectangular list that has one header row, one record per row, and no completely blank rows splitting the data. The range passed to AutoFilter determines which columns the field numbers refer to. If the range is A1:C100, Field 1 means column A, Field 2 means B, and Field 3 means C.

Suppose A1:C100 contains Order ID, Status, and Region, with Status in column B. To exclude Closed orders, use Field 2. If instead the range starts at B1, Status is Field 1. Choosing a field based on its worksheet letter rather than its position in the range is a frequent cause of filtering the wrong column.

  • Include the header row in the range so Excel knows where the field names are.
  • Check that the field number is between 1 and the number of columns in the range.
  • Make sure the criterion is applied to the intended column, especially if the range begins somewhere other than column A.
  • Use a consistent data layout; merged cells and blank rows can make a list harder to filter reliably.

A complete VBA example

This macro assumes a worksheet named Data, a header row in A1:C100, and Status in the second column of that range. It applies the filter without clearing filters already applied to other fields. A new criterion for the same field replaces that field’s previous criterion.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:C100")

    ' Exclude rows whose Status field is Closed.
    dataRange.AutoFilter Field:=2, Criteria1:="<>Closed"
End Sub

Change the worksheet name, range, field number, and excluded value to fit the workbook. If the data grows or shrinks, a fixed address such as A1:C100 may stop including all records; use a suitable dynamic range or an Excel table when the list changes regularly. Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z.

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11. On a Mac, the shortcut is usually Option+F11; some keyboards require Fn+Option+F11. Excel for the web does not run VBA macros, so open the workbook in the desktop application to use this code.

Excluding a value while handling blanks

A common misconception is that “not equal to Closed” always means “a nonblank value other than Closed.” Blank cells are a distinct case in Excel filtering, and they can appear in results when the criterion is only "<>Closed". If blank statuses must not appear, add an explicit nonblank condition and check the result against the actual data:

dataRange.AutoFilter Field:=2, _
    Criteria1:="<>Closed", _
    Operator:=xlAnd, _
    Criteria2:="<>"

The second criterion, "<>", means nonblank. With xlAnd, a row must satisfy both conditions: its value is not Closed and it is not blank. Test this on a copy when blank handling matters, particularly when cells contain formulas that return an empty string; formula-empty cells can behave differently from truly empty cells in filtering and other Excel operations.

If you want to exclude Closed but keep blanks, use the simple not-equal criterion and inspect the filtered rows. If the desired blank behavior is critical and the source includes mixed data types or formulas, a helper column can make the rule explicit and easier to audit.

Exclude two values or use a changing value

For two exclusions, AutoFilter can combine two criteria with AND. For example, to omit both Closed and Pending from the Status field:

dataRange.AutoFilter Field:=2, _
    Criteria1:="<>Closed", _
    Operator:=xlAnd, _
    Criteria2:="<>Pending"

A row must meet both tests, so neither excluded status is shown. This pattern is suitable for two values. If you need to exclude a longer or frequently changing list, consider a helper column rather than building a complicated criteria string. It makes the exclusion list visible and easier for another person to maintain.

For a value stored in a VBA variable, concatenate the operator with the value. For example, after declaring a String variable named excludedValue and assigning it a value, use Criteria1:="<>" & excludedValue. Avoid assuming that user-entered text is always a literal match: AutoFilter criteria can interpret wildcard characters such as an asterisk as pattern matching. If the value itself may contain wildcard characters, handle that case deliberately and test it with representative data.

Common errors and how to fix them

  • Filtering the wrong column: Recount the fields from the left edge of the range. Field 2 means the second column in the range, not worksheet column B in every situation.
  • The header is missing: Include the header row in the range. Without it, Excel may treat the first record as the header and leave it out of the expected results.
  • The value looks identical but does not match: Cells may contain leading or trailing spaces, nonprinting characters, or inconsistent values. Clean or normalize the source data if those differences should not matter.
  • The criterion seems to include unexpected rows: Check blanks, formula results, and wildcard characters. Also verify that the column contains the data type you expect, rather than a mixture of text and numbers.
  • Existing filters affect the result: AutoFilter criteria on different fields work together. A row must satisfy all active field filters. Clear or inspect other filters before concluding that this criterion is wrong.
  • The macro errors when changing sheets: Confirm the worksheet name exists in the workbook and that the range is valid. If the workbook has several sheets with similar names, refer to the intended sheet explicitly.

To clear all active filters on a worksheet while keeping the filter controls, check whether ws.FilterMode is True and then call ws.ShowAllData. This removes the filtering conditions across the sheet, not just the not-equal condition. If you only want to change one field, apply a new criterion to that field instead of clearing everything.

When a helper column is a better choice

A helper column is useful when exclusions are numerous, users need to see the rule, or the logic includes additional conditions. For example, with statuses in A2:A100, enter this in B2 and fill it down:

With statuses in A2:A100 and a helper check in B2, =AND(A2<>"Closed",A2<>"Pending") returns TRUE for values other than Closed and Pending. Filter the helper column for TRUE. This version also returns TRUE for a blank A cell; to reject blanks too, use =AND(A2<>"",A2<>"Closed",A2<>"Pending").

Because the rule is visible in worksheet cells, a helper column can be easier to review than a long VBA criteria expression. It is also convenient when the exclusion list changes often: the worksheet can show which rows pass the test before any rows are hidden. The tradeoff is that it adds a column and may require updating the formula range when new records are added.

AutoFilter versus hiding or deleting rows

AutoFilter hides rows that do not meet the criterion; it does not delete them or change their cell values. That makes it appropriate for reviewing a subset while preserving the complete data set. Clearing the filter makes hidden records visible again.

Deleting rows is a different operation and can be difficult to reverse after a macro runs. If the goal is to permanently remove records, first make a copy and verify the exact rows selected. If the goal is only to exclude a category from a view, filter it rather than deleting it. Also remember that a filtered worksheet can make it easy to overlook hidden records when copying, editing, or reporting data, so check the active filter state before sharing results.

Frequently Asked Questions

What is the VBA AutoFilter syntax for not equal?
Use Criteria1:="<>value" with the desired field number, such as dataRange.AutoFilter Field:=2, Criteria1:="<>Closed". The field number is relative to the left edge of dataRange.

How do I filter out a number instead of text?
Use the same not-equal operator with the numeric value, for example Criteria1:="<>100". Confirm that the cells contain numbers rather than text that only looks numeric.

Does not equal include blank cells?
Blank cells can produce unexpected results when the only condition is not equal to a value. If blanks must be excluded, add an explicit nonblank criterion and test the filter on representative rows, including cells with formulas returning an empty string.

Can I exclude more than two values?
Two exclusions can be combined with xlAnd and two criteria. For a longer list, a helper column is usually clearer: test the row against the exclusion list, then filter the helper result.

Can I run this macro in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not execute VBA. Use the desktop Excel application to run the macro.

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.