VBA AutoFilter Exclude Criteria: How to Filter Out Values

VBA & macros · Updated November 18, 2026

To exclude a value with VBA AutoFilter, use a not-equal criterion such as <>Closed: filterRange.AutoFilter Field:=3, Criteria1:="<>Closed". The field number is the column’s position inside the filtered range, so this example filters its third column and hides rows whose value is Closed.

How exclusion criteria work

AutoFilter applies criteria to a specified field in a range that includes a header row. In a custom criterion, the operator <> means does not equal. The criterion is a string, so VBA passes it as a value such as "<>Closed". Excel then displays records that meet the criterion and hides matching records; it does not delete data.

The field number is relative to the first column in the range, not necessarily the worksheet column number. If the filter range starts in column B, its first field is B, its second is C, and its third is D. This is a frequent source of errors when a macro uses an absolute worksheet column number instead.

For instance, if the range is A1:D100 and Status is in column C, use Field:=3. If the range is B1:E100 and Status is in column D, Status is also the third field, so use 3 again. The range should include the column headings; otherwise, Excel may treat the first data row as a heading.

Exclude one exact value with a macro

This example filters a contiguous table on a worksheet named Data. It clears any existing filter result on that sheet before applying the new criterion, so the outcome is not accidentally constrained by an older filter.

Sub ExcludeClosedRows()
    Dim ws As Worksheet
    Dim filterRange As Range
    Dim fieldNumber As Long

    ' Use the contiguous table beginning at A1.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set filterRange = ws.Range("A1").CurrentRegion
    fieldNumber = 3 ' Status is the third column in this range.

    ' Clear existing criteria if rows are currently filtered.
    If ws.FilterMode Then ws.ShowAllData

    ' Keep records whose Status is not Closed.
    filterRange.AutoFilter Field:=fieldNumber, _
        Criteria1:="<>Closed"
End Sub

Before running a macro, save a copy of the workbook. Filtering itself does not erase rows, but VBA actions cannot be reversed with Ctrl+Z, and later edits or changes made by an expanded macro may not be recoverable through Undo. To run this on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and run the procedure. In Excel for Mac, use Developer > Visual Basic or the Option+F11 shortcut (some keyboards also require Fn). Excel for the web cannot run VBA macros. If the Developer tab is missing, enable it through File > Options > Customize Ribbon on Windows; on Mac, use Excel > Settings > Ribbon & Toolbar.

Exclude two values from the same column

AutoFilter can apply two criteria to one field. Combine two not-equal conditions with Operator:=xlAnd. This means a row must satisfy both conditions: its value must not be Closed and must not be Pending.

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

Put this in place of the single-criterion AutoFilter line in the macro. The conditions are joined with AND, not OR. That distinction matters: a status cannot be both Closed and Pending at once, so asking for a value that is not Closed OR not Pending would allow nearly every status through. With AND, the filter hides both named values and retains other statuses.

AutoFilter’s usual custom-filter interface supports two criteria for a field. If you need to exclude a longer list, a helper column is often easier to understand and maintain than trying to stretch the two-criterion pattern.

Exclude several values with a helper column

A helper column lets you express a longer exclusion list explicitly. Suppose the status is in A2 and the list to exclude is Closed, Pending, and Cancelled. Put this formula in a helper column on the same row, fill it down, then filter the helper column for TRUE:

With the status value in A2, this formula returns TRUE for Open and FALSE for Closed: =AND(A2<>"Closed",A2<>"Pending",A2<>"Cancelled").

Each comparison must be true for the result to be TRUE, which gives the same all-conditions-must-pass logic as the two-criterion AutoFilter example. To add another excluded value, add another comparison inside AND. If blank statuses should also be removed, add A2<>"" as another condition. This makes the blank-handling choice clear rather than relying on how a particular filter displays empty cells.

For a macro that runs repeatedly, you can keep the exclusion list in worksheet cells and have VBA build or evaluate the helper result. That makes changes to the list possible without editing code. Keep the helper heading distinct and include the helper column in the filtered range so its TRUE/FALSE values can be filtered normally.

Wildcards, blanks, and text matching

Criteria can also use wildcards for text patterns. For example, "<>Closed*" excludes text values that start with Closed, such as Closed – late, while allowing unrelated values. The asterisk stands for any number of characters. A question mark matches one character. These patterns are useful when the source contains variations, but they are broader than excluding one exact status.

If the value being searched for contains a wildcard character that should be treated literally, Excel criteria use a tilde as an escape character. For example, a literal asterisk in a text value is represented with ~* in a criterion. Test pattern criteria on a small range first, especially when the data contains punctuation or inconsistent spacing. AutoFilter text matching is generally not case-sensitive, so do not use it when distinctions such as open versus Open must be preserved.

Blanks deserve separate attention. A not-equal criterion is not the same as an explicit instruction to show or hide blanks, and a blank may remain visible when excluding a named text value. If the requirement is to exclude both Closed and empty cells, use a helper condition that tests both, such as the additional A2<>"" comparison above. If blank behavior is important to the result, verify the visible rows against a small known example.

Choosing AutoFilter, a helper column, or Advanced Filter

For one or two exclusions in an interactive table, AutoFilter is usually the most direct choice. It preserves the original rows and lets users see the filter arrows and change the criteria. A helper column is a better fit when the exclusion list is longer, changes often, or involves several logical conditions that should be visible and auditable.

Excel’s Advanced Filter is another option for more involved criteria or for copying matching records to another location. In Microsoft 365 for Windows, find it at Data > Sort & Filter > Advanced. Its criteria-range rules distinguish AND from OR: criteria on the same row are combined, while criteria on separate rows are alternatives. That behavior can make a list of not-equal conditions behave differently than expected if arranged incorrectly. For complicated exclusion logic, a helper column often makes the intended result easier to inspect before filtering or copying.

Do not confuse excluding rows with deleting them. AutoFilter hides nonmatching records; a separate delete action is needed to remove rows. If deletion is the goal, first check the filtered results and save a backup. A filter is a reversible view of the data, whereas deleting visible rows changes the workbook.

Common problems and how to fix them

  • Wrong Field number: Count from the left edge of the range passed to AutoFilter. A worksheet column number is not automatically the correct field index.
  • Range does not include all records: CurrentRegion stops at fully blank rows or columns. If the table has gaps, set the range explicitly or use an Excel table’s data range.
  • Headers are missing or duplicated: Include a single header row in the filter range and check that the intended column heading is unambiguous.
  • Old criteria remain active: Clear the prior filter with ShowAllData when FilterMode is True before applying a different criterion.
  • Values look identical but do not match: Check for trailing spaces, nonbreaking spaces, inconsistent spelling, or numbers stored as text. Clean or normalize the source values if those differences should count as the same status.
  • Nothing appears to happen: Confirm that the selected field contains the expected values and that the criteria string has the operator and text together, such as "<>Closed".

When a range is an Excel table, filtering is already supported by its header controls, and the table can expand as rows are added. For VBA, use the table’s range rather than assuming a fixed last row if the data changes frequently. Whatever range approach you choose, make sure the field index still refers to the intended column.

Frequently Asked Questions

How do I filter out one word in VBA?
Use Criteria1:="<>word" on the correct field. For example, Criteria1:="<>Closed" keeps rows whose field value is not Closed.

Can AutoFilter exclude more than two values?
A custom AutoFilter criterion normally supports up to two conditions for one field. For a longer exclusion list, use a helper column that returns TRUE only when the row is not any excluded value, then filter for TRUE.

Does VBA AutoFilter delete excluded rows?
No. It hides rows that do not meet the filter criteria. The records remain in the worksheet unless a separate macro or manual action deletes them.

What does Field:=3 mean in AutoFilter?
It means the third column of the range being filtered. It does not necessarily mean worksheet column C; the range’s leftmost column is Field 1.

Can I use VBA AutoFilter in Excel for the web?
Excel for the web can display and use worksheet filters, but it does not run VBA macros. Run the macro in the desktop Excel application 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.