Excel VBA AutoFilter: Exclude Values, Blanks, and Text

VBA & macros · Updated November 21, 2026

To exclude a value with Excel VBA AutoFilter, pass a not-equal criterion such as "<>Closed" to the field you want to filter: filterRange.AutoFilter Field:=3, Criteria1:="<>Closed". The field number is relative to the first column in the filtered range, so Field:=3 means its third column.

What AutoFilter exclusion means

AutoFilter hides rows that do not meet a criterion; it does not delete them or change the underlying values. An exclusion therefore means showing rows whose value in a chosen field does not match the value or pattern you specify. The hidden rows remain in the worksheet and can be shown again by clearing the filter.

In filter criteria, the less-than and greater-than signs together, <>, mean “not equal to.” For example, "<>Closed" keeps rows where the filtered cell is not Closed. Text matching is not case-sensitive in a standard Excel filter, so this criterion also excludes CLOSED or closed. It is intended to exclude the whole matching cell value, not every value that happens to contain those letters.

AutoFilter is useful when you want a repeatable view of a list: omit a status, hide blank records, or screen out a category before reviewing or copying visible rows. Since it changes the displayed rows rather than the data, it is usually safer than deleting records just to make them temporarily disappear.

How the VBA criteria work

The Range.AutoFilter method applies a filter to a range that includes the header row. Its Field argument identifies the column within that range, and Criteria1 supplies the first condition. The order and size of the range matter: if the range starts in column C, its first field is C, not worksheet column A.

For an exact text exclusion, put the value after <>. For a number, use a numeric criterion such as "<>0". The criterion is a string argument in VBA, even when it represents a number. If you need date-based criteria, take care with date serials and locale-specific date text; a helper column or a carefully tested date comparison can be more reliable than building a criterion from an ambiguous date string.

Applying a criterion to a field generally changes that field’s filter while other active field filters can remain in place. If the range already has filters, the visible result is the intersection of the conditions: a row must pass every active field’s condition to remain visible. This can make a correct exclusion appear too restrictive if another column is also filtered.

Basic VBA example for excluding one value

This example assumes a worksheet named Data with a contiguous table-like list beginning at A1, a header in row 1, and the status field in the third column of that list. It uses CurrentRegion to find the adjoining data block, so check that there are no blank rows or columns splitting the list and no unrelated data touching it.

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

    ' Change the sheet name and starting cell to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set filterRange = ws.Range("A1").CurrentRegion

    ' Exclude rows whose third field is exactly Closed.
    filterRange.AutoFilter Field:=3, Criteria1:="<>Closed"
End Sub

Before running a macro, save a copy of the workbook. A filter operation does not delete records, but VBA actions cannot be undone with Ctrl+Z, so a backup is a sensible precaution. In Excel for Windows, open the VBA editor with Alt+F11, insert the procedure into a standard module, and run it while the workbook is open. If the sheet or field number differs in your file, change those details before running it.

If the list is an Excel Table, you can instead refer to the table’s data range or use its worksheet range, making the table name explicit and avoiding some uncertainty about adjacent cells. Either way, include the header row in the range passed to AutoFilter; the field number counts from the left edge of that range.

Excluding more than one value

For two values, the ordinary AutoFilter method can combine two not-equal conditions with xlAnd. Both conditions must be true for a row to remain visible, which is what you want when excluding either of two exact values.

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

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

    ' Keep rows that are neither Closed nor Pending.
    filterRange.AutoFilter Field:=3, _
        Criteria1:="<>Closed", Operator:=xlAnd, _
        Criteria2:="<>Pending"
End Sub

This is different from using xlOr: “not Closed OR not Pending” would be true for almost every row, including rows matching one of the values you hoped to remove. The AND condition is the correct logic for keeping values that match neither excluded item.

For three or more exclusions, do not assume that AutoFilter accepts an unlimited list of “not equal” criteria. A practical alternative is to add a helper column that marks records as keep or exclude, then filter that field to Keep. Another option is to build a list of allowed values and filter using those values with xlFilterValues; that is an inclusion list, so it must be updated if new allowed values appear. Choose based on whether the list of exclusions or the list of allowed values is more stable.

Excluding blanks, partial text, and patterns

Blank handling is a common source of surprises. The criterion "<>" filters for nonblank cells, so it excludes blanks. The criterion "=" selects blanks. If the goal is “exclude Closed but also exclude blanks,” combine the conditions rather than assuming the text exclusion necessarily communicates your blank policy. For a single exact text exclusion and nonblank requirement, use two conditions with AND, after checking the results against representative rows.

Wildcards let a text criterion match a pattern. An asterisk represents any number of characters, and a question mark represents one character. For example, "<>*East*" excludes cells whose text contains East anywhere in the value. A criterion such as "<>INV-?" excludes values matching the INV- prefix followed by one character. If the text itself contains a wildcard character that should be treated literally, Excel uses a tilde to escape it.

Pattern criteria can catch more than expected. The contains-East example will also exclude “Northeast,” because East occurs inside that string. If you mean to exclude a whole category only when it equals East, use the exact criterion "<>East" instead. Test a wildcard criterion on a small sample before relying on it for a large report.

Choosing the right range and field

Most filter errors come from an incorrect range or field number, not from the not-equal operator. If the list begins at B1 and the value to filter is in worksheet column D, the field number is 3: B is field 1, C is field 2, and D is field 3. The column’s worksheet letter is not the field number unless the range starts in column A.

  • Include the header row, and make sure it contains meaningful column names.
  • Use a range that covers all records and all relevant columns, with no accidental extra block of data.
  • Check for blank rows or columns that would split a CurrentRegion into smaller blocks.
  • Confirm the filtered column’s actual values, including trailing spaces or inconsistent entries such as “Closed ”.
  • If filtering an Excel Table, verify that the code targets the intended table rather than a neighboring range.

For a recurring process, an explicit range or a named Table can be clearer than relying on the active cell or the current selection. Avoid code that uses Selection unless the macro is deliberately meant to act on whatever the user selected; it can filter the wrong data when the active selection changes.

Common problems and how to fix them

If the macro runs but shows no visible rows, first inspect the filter arrows and clear any other active filters. Then check whether every record actually matches another condition, whether the field number is correct, and whether values contain extra spaces or inconsistent spellings. A value that looks like Closed on screen may include a trailing space and therefore not behave as an exact match.

If headers disappear into the data, the range may not begin at the header row. If the wrong column is filtered, recalculate the field number relative to the range’s left edge. If the code toggles arrows off rather than applying the expected filter, ensure the call includes a field and criteria, and check whether another part of the macro is calling AutoFilter without arguments.

A filter does not remove records. If you copy visible cells after filtering, be attentive to whether your later operation targets visible cells only; a separate copy or delete routine can have consequences beyond the filter itself. Always verify the result before running any follow-up macro that edits or deletes rows.

Excel versions, Mac, and alternatives

The examples target Excel for Microsoft 365 on Windows. Excel for the web can display and change worksheet filters, but it cannot run VBA macros; open the workbook in desktop Excel to execute the code. Excel for Mac has VBA support, but Microsoft documents limitations for the Range.AutoFilter method on Mac. If that method is unavailable in your Mac build, apply the filter through the Data tab or use a supported desktop environment rather than assuming the Windows VBA call will work unchanged.

For manual filtering on Windows, select a cell in the data and use Data > Filter; Ctrl+Shift+L toggles filter arrows. On Mac, use Data > Filter; keyboard shortcuts can vary with the keyboard and Excel version, so the menu is the dependable path. In the filter dropdown for the relevant column, use the search or checkbox list to deselect values, or use the text-filter options for a not-equal condition.

Use VBA when the same exclusion must be applied repeatedly or as part of a larger macro. Use the interface for a one-time review, especially when you want to inspect the available values before deciding what to exclude. A helper column is often the clearest option when exclusion rules are numerous, depend on several columns, or need to be explained to other workbook users.

Frequently Asked Questions

What does <> mean in an Excel AutoFilter criterion?
The operator means “not equal to.” For example, "<>Closed" excludes cells whose full text value is Closed, while leaving other values visible if no additional filter hides them.

How do I exclude blank cells with VBA AutoFilter?
Use Criteria1:="<>" to show nonblank cells in the selected field. Use Criteria1:="=" when you want to show blanks instead. Test the result if cells contain formulas that return empty text, because those may not behave like truly empty cells in every filtering context.

Can VBA AutoFilter exclude several values?
Two exact values can be excluded by combining two not-equal criteria with Operator:=xlAnd. For a longer exclusion list, use a helper column or construct a deliberate list of allowed values; a normal AutoFilter call is not an unlimited exclusion-list interface.

Does AutoFilter delete the excluded rows?
No. It hides rows that fail the criteria. Clear the filter to display them again. A separate VBA deletion command would be needed to remove data, and that should be used only after backing up and verifying the target rows.

Why does excluding a word also hide longer phrases?
That usually happens when the criterion contains wildcards, such as *East*, which matches the word anywhere inside a cell. Use an exact not-equal criterion such as "<>East" when only the complete cell value should be excluded.

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.