Excel VBA AutoFilter: Does Not Contain Criteria

VBA & macros · Updated November 21, 2026

To filter out rows where a column contains specific text, use Excel VBA AutoFilter with the criterion "<>*text*"; for example, Criteria1:="<>*draft*" shows entries that do not contain “draft.” The asterisks make the match partial, while <> means “not equal to.”

The does-not-contain criterion

In Excel for Microsoft 365 on Windows, AutoFilter accepts criteria in the same general style as the Custom AutoFilter dialog. To find text that does not occur anywhere within a cell, put a wildcard before and after the text and prefix the pattern with the not-equal operator. The basic form is <>*text*. AutoFilter applies this to the field identified by its position in the filtered range.

For example, if a range has headers in row 1 and the second column is called Status, the criterion <>*closed* filters out entries containing “closed,” including values such as “Closed,” “not closed,” and “closed – pending.” Excel’s text filtering is not case-sensitive, so capitalization alone does not make a match different. The criterion is a substring test, not a test for an exact cell value.

The key VBA argument is Criteria1. You normally do not need to specify an Operator for this single criterion. The method can also apply to a worksheet range or an Excel table, but the meaning of the field number depends on the range you call it on.

How the wildcards and operators work

AutoFilter recognizes wildcard characters in text criteria. An asterisk (*) represents any number of characters, and a question mark (?) represents one character. A tilde (~) is used to treat a wildcard character literally. Combined with the not-equal operator, the parts of the criterion have distinct jobs: <> says to exclude a match, and *text* finds the text even when other characters appear before or after it.

  • <>*red* excludes text containing “red,” such as “red apple” or “already paid.”
  • <>*AB?* excludes text containing “AB” followed by any one character, because the question mark is a wildcard.
  • To search for an actual asterisk, question mark, or tilde, escape it with a tilde rather than letting Excel interpret it as a wildcard.

That last point matters when the search term comes from a cell or user input. A search for plan* can otherwise mean “plan followed by anything,” not the literal characters “plan*.” The escaping example below handles the three wildcard characters so the entered term is treated as literal text.

Complete VBA example for a worksheet range

This example assumes a worksheet named Data, a header row in A1, contiguous data extending from that cell, and the text to exclude in the second column of that range. Change the worksheet name, field number, or search text to match your workbook. Field:=2 means the second column of the filtered range, not necessarily worksheet column B.

Sub FilterRowsNotContainingText()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim searchText As String
    Dim criteria As String

    ' Change these values to match the worksheet and search term.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion
    searchText = "draft"

    ' A blank term would make the intended filter unclear.
    If Len(searchText) = 0 Then Exit Sub

    ' Escape wildcard characters so the term is treated literally.
    criteria = "<>*" & EscapeAutoFilterText(searchText) & "*"
    dataRange.AutoFilter Field:=2, Criteria1:=criteria
End Sub

Private Function EscapeAutoFilterText(ByVal value As String) As String
    ' Escape the escape character first, then the wildcard characters.
    value = Replace(value, "~", "~~")
    value = Replace(value, "*", "~*")
    value = Replace(value, "?", "~?")
    EscapeAutoFilterText = value
End Function

Before running a macro, save a separate copy of the workbook. VBA changes made by a macro cannot be reversed with Ctrl+Z, so a backup is the practical way to protect the original data. The code applies a filter; it does not delete the rows it hides. If the range already has filters, Excel can retain filters on other fields while applying this criterion to Field 2.

Using a table or choosing the field number

An AutoFilter field number is counted from the left edge of the range passed to AutoFilter. If the range is B1:E100, then Field 1 is column B, Field 2 is C, and Field 3 is D. This is a frequent source of mistakes when code uses a worksheet column number where AutoFilter expects a range-relative field number.

For an Excel table, use the table’s range as the filter range. For example, if a table is named Orders, a field number of 3 refers to its third table column, regardless of the table’s starting worksheet column. Table column names can make code easier to understand, but AutoFilter still uses a field position for this method. Confirm that the first row of the chosen range contains the headers and that the intended data is within the range.

CurrentRegion is convenient for a single contiguous block of data, but it stops at blank rows or columns. If the dataset has gaps, or if other content sits next to it, define the range explicitly or use a table instead. A range that omits the header or part of the data can make the filter appear to target the wrong values.

What happens to blank cells?

A does-not-contain criterion is straightforward for text entries, but blank cells are a separate case. If it matters whether empty cells appear in the results, do not rely on an assumed blank-cell behavior: check the result against a small sample of your workbook. A cell that looks empty may contain a formula returning an empty string, spaces, or another invisible character, and those cases are not necessarily equivalent to a truly empty cell.

For an explicit rule, add a helper column that returns TRUE or FALSE, then filter that column. For example, if the text to search is in D1 and the values being checked are in A2:A100, enter this formula in the helper column’s first data row and fill it down: =IF(A2="",TRUE,ISERROR(SEARCH($D$1,A2))). This treats empty-looking values as “does not contain” and uses SEARCH, which is case-insensitive. To exclude blanks instead, with the same layout use =AND(A2<>"",ISERROR(SEARCH($D$1,A2))). Filter the helper column for TRUE. Decide how formula-generated empty strings and cells containing spaces should be treated before choosing the rule.

Applying more than one filter condition

A single does-not-contain criterion needs no compound operator. If you need to exclude two different phrases from the same column, you may need two criteria joined with an AND condition. AutoFilter exposes Criteria1, Operator, and Criteria2 for combinations, but wildcard and not-equal criteria can be confusing when combined. Verify the results on representative data rather than assuming a second criterion has the intended logic.

Another option is a helper column that checks both phrases explicitly. With the value in A2, and search terms in D1 and E1, a formula such as =AND(ISERROR(SEARCH($D$1,A2)),ISERROR(SEARCH($E$1,A2))) returns TRUE only when neither term is found. Fill it down and filter for TRUE. This is more transparent for complex conditions, although it adds a column and requires a clear policy for blanks.

Common problems and how to fix them

  • No rows are hidden: Check that the term is actually present in the target field and that the criterion has both wildcard asterisks. A criterion like <>draft tests a different condition from <>*draft*.
  • The wrong column is filtered: Recount Field from the leftmost column of the range used in the AutoFilter call. Do not substitute a worksheet column letter or number without accounting for the range’s starting position.
  • Text with a star or question mark behaves unexpectedly: Escape literal wildcard characters with a tilde, as in the function shown above. Also escape a literal tilde itself.
  • The filter includes unexpected records: Check for leading or trailing spaces, formulas returning empty text, and values that only look alike. Use a helper column if the rule must define these cases precisely.
  • The macro errors or filters only part of the data: Confirm that the range includes the header and all records, and that CurrentRegion has not been broken by a blank row or column.

For macro access, Excel for Microsoft 365 on Windows opens the Visual Basic Editor with Alt+F11; the Developer tab also has Visual Basic. On a Mac, use Developer > Visual Basic or Option+F11 (on some keyboards, Fn may also be needed). Excel for the web can display a workbook containing VBA, but it cannot run or edit VBA macros; use the desktop application for this code.

When a helper column is the better choice

Use AutoFilter’s <>*text* criterion for a quick, readable filter on one text column when the standard text-match behavior is suitable. A helper column is preferable when blank handling must be explicit, several exclusions must be combined, or a reviewer needs to see exactly why a row passed or failed. It is also easier to audit than a complicated string assembled in VBA.

There is no need to delete rows to get a clean view. Filtering hides nonmatching records while leaving the underlying data in place. If you later copy or export visible rows, check whether the destination should contain only visible records and verify the result before replacing or distributing the source data.

Frequently Asked Questions

What is the VBA AutoFilter code for does not contain?
Use Criteria1:="<>*text*" in the AutoFilter call. Replace text with the phrase to exclude, and set Field to the target column’s position within the filtered range.

Does Excel VBA AutoFilter ignore capitalization?
Text AutoFilter criteria are generally case-insensitive, so a search for “draft” also matches “Draft.” Use a different method, such as a helper formula with a case-sensitive comparison approach, if capitalization must matter.

How do I filter a table column that does not contain a word?
Call AutoFilter on the table’s range and use the field position for that table column. The field number starts at the table’s first column, not at the corresponding worksheet column.

How can I make the filter include blank cells?
Check how the criterion behaves with your workbook’s true blanks and formula-generated empty strings. For a guaranteed rule, use a helper formula that explicitly returns TRUE for blank cells, then filter the helper field for TRUE.

Can I use this VBA macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the Visual Basic Editor and execute 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.