To exclude one exact value with Excel VBA AutoFilter, set the field’s criterion to a not-equal condition: Criteria1:="<>Closed". For example, dataRange.AutoFilter Field:=3, Criteria1:="<>Closed" filters the third column of the specified range to show rows whose value is not Closed.
How AutoFilter exclusion criteria work
AutoFilter applies criteria to a column in a range or Excel table. In VBA, the Field argument identifies the filtered column by its position within the range passed to AutoFilter, not by its worksheet column letter. If the filtered range begins in column A, field 3 is column C; if it begins in column D, field 3 is column F.
The not-equal operator is the text <>, which is also used in Excel’s worksheet criteria. Put it before the value you want to omit. For instance, the criterion <>Closed excludes the value Closed while leaving other values eligible to appear. Text comparisons in Excel’s AutoFilter are generally case-insensitive, so this is not a way to distinguish Closed from CLOSED.
The range should include the header row and the rows to filter. AutoFilter uses the header to identify the column, so a range that starts on the first data row can make the field number point at the wrong column or treat data as a header. The criterion filters rows; it does not delete excluded records or alter their cell contents.
A basic VBA example
This example filters a worksheet range with headers in row 1 and data through row 100. It excludes rows where the third field, headed Status in this example, equals Closed. Change the worksheet and range references to match the workbook rather than relying on whichever cells happen to be selected.
Sub ExcludeClosedRows()
Dim ws As Worksheet
Dim dataRange As Range
' Use the worksheet and range that contain the table.
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:D100")
' Field 3 is the third column within dataRange.
dataRange.AutoFilter Field:=3, Criteria1:="<>Closed"
End Sub
In this example, the range’s first row must contain the column headings, and its third column must be the status values. If the data grows beyond row 100, expand the range or use an Excel table, whose data range can grow with added rows. Save a copy of the workbook before running a macro. Macros cannot be undone with Ctrl+Z; removing a filter is not the same as undoing changes made by a macro.
Exclude two values from the same column
To leave out two exact values, combine two not-equal criteria with xlAnd. Both conditions must be true for a row to remain visible. In the example below, the same four-column range is filtered to omit both Closed and Pending from its third field:
Sub ExcludeTwoStatuses()
Dim ws As Worksheet
Dim dataRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:D100")
' Keep values that are neither Closed nor Pending.
dataRange.AutoFilter Field:=3, _
Criteria1:="<>Closed", Operator:=xlAnd, _
Criteria2:="<>Pending"
End Sub
AutoFilter’s ordinary criteria arguments are useful for one or two conditions. They are not a general list of unlimited exclusions. If you need to omit several values, consider adding a helper column that marks allowed rows, or build a separate output using a more suitable data-processing method. A helper column makes the rule visible and easier to review: it can label each row Keep or Exclude, after which AutoFilter can show only Keep.
Do not replace xlAnd with xlOr in this two-exclusion example. A value cannot be both different from Closed and different from Pending if it is one of those excluded values; the AND combination expresses the intended logic. With OR, the condition would be true for Closed because Closed is not Pending, so that row could remain visible.
Excluding blanks, partial matches, and numbers
Blank handling is a frequent source of surprises. The criterion <> is commonly used to show nonblank cells. If the requirement is to exclude both a named value and blanks, specify the nonblank condition as well as the not-equal condition, then check the result against representative rows in your data:
Sub ExcludeClosedAndBlanks()
Dim ws As Worksheet
Dim dataRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:D100")
' Require a nonblank value that is not Closed.
dataRange.AutoFilter Field:=3, _
Criteria1:="<>Closed", Operator:=xlAnd, Criteria2:="<>"
End Sub
Cells that look blank may contain a formula returning an empty string, spaces, or other invisible characters. Those are not necessarily the same as genuinely empty cells, and filtering can therefore differ from what a quick visual inspection suggests. If the distinction matters, inspect the cell contents or add a helper column that classifies the cases explicitly.
For a partial text match, AutoFilter criteria support the asterisk wildcard for any number of characters and the question mark wildcard for one character. For example, <>*draft* excludes text containing draft, such as Draft review or revised draft. Wildcard matching is useful for categories with varying labels, but it can also exclude more than intended. A tilde can escape wildcard characters when you need to treat a wildcard character literally.
For a numeric field, a criterion such as <>0 excludes zero while retaining other values. Make sure the cells contain numbers rather than text that only looks numeric; imported data can mix the two. Dates are stored as numeric values in Excel, but date filters and displayed formats can make criteria less obvious. When filtering dates, use a known sample date and verify which rows remain rather than assuming the displayed text is the underlying value.
Choosing the correct range and field
Many apparent criteria problems are actually range or field problems. If the data occupies columns B through E, then Field:=3 means column D, not worksheet column C. The field count always starts at 1 for the first column of the range. Confirm the range’s top-left cell and count across to the target column before changing the criterion.
For a list that changes size, a fixed address such as A1:D100 can miss new rows. One option is to convert the data to an Excel table using Insert > Table, then work with the table’s range in VBA. Another is to determine the last used row and construct a range that covers it. Whichever method you choose, keep the header row included and avoid accidentally including unrelated notes or totals below the list.
Also check whether the worksheet already has filters. Applying AutoFilter to a range can interact with existing filter arrows and criteria. If the target column has an old condition, applying a new criterion to that field replaces its condition, while filters on other fields can remain in effect. That means a row might be hidden by a different column even when it satisfies the new exclusion rule.
Clearing and changing filters safely
Applying a criterion hides rows from view; it does not remove them. To show all rows again while retaining the filter arrows, clear the filter conditions on the worksheet. In VBA, ShowAllData can show all data when a filter is active, but calling it when there is no active filtered list can raise an error. A robust macro should check the worksheet’s filter state before calling it, especially if it will run in different workbook conditions.
To remove the AutoFilter arrows entirely, use the range’s AutoFilter method without criteria when appropriate, or use the worksheet’s filter controls. Be careful not to toggle filtering blindly: calling the method without criteria can turn arrows on or off depending on the current state. For a macro used repeatedly, decide whether it should clear an existing filter, preserve other column filters, or replace the whole filter state, and code for that explicit behavior.
When troubleshooting, first inspect the visible rows and the filter dropdown for the target column. Then confirm that the macro points to the intended worksheet, that the range includes the correct header, and that the field number matches the target column within that range. Finally, test a row with the excluded value, one with an allowed value, and a blank if blanks matter. This small check distinguishes a criteria issue from a range, data-type, or additional-filter issue.
When to use another filtering method
AutoFilter is a good fit when you want to hide rows in place and let someone inspect the remaining records. For a few exclusions, not-equal criteria are concise and readable. For a long or frequently changing exclusion list, however, maintaining a chain of criteria is awkward and ordinary AutoFilter criteria are limited. A helper column is often clearer: it can compare each row against a maintained list of blocked values and then filter on the helper result.
Advanced Filter is another built-in option for more complex criteria layouts, including criteria arranged in a separate worksheet range. It can copy matching records to another location instead of hiding rows in the source. Use it when the criteria need to be documented in cells or when a separate result set is preferable. For repeatable reporting, Power Query can also filter rows during a refresh; it is a different workflow from VBA AutoFilter and is useful when the source data is imported or transformed regularly.
These alternatives do not make AutoFilter wrong. Choose based on what the workbook needs to do: interactively hide rows, express a complicated rule transparently, generate a separate extract, or refresh a data transformation. If the rule changes often, make the excluded values part of the workbook’s data rather than burying a long list inside macro code.
Frequently Asked Questions
What is the VBA criterion for “does not equal”? Use <> before the value. For example, Criteria1:="<>Closed" excludes Closed in the selected field.
How do I exclude two values? Use two not-equal criteria joined with Operator:=xlAnd, such as Criteria1:="<>Closed", Operator:=xlAnd, Criteria2:="<>Pending". The field must satisfy both conditions to remain visible.
Does Field refer to a worksheet column number? No. It is the column’s position within the range passed to AutoFilter. If the range starts in column B, field 1 is B and field 3 is D.
Can AutoFilter exclude text that contains a word? Yes. Use a wildcard criterion such as <>*draft* to exclude cells containing draft. Check for unintended matches because the wildcard can match text before and after the word.
Does filtering delete the excluded rows? No. AutoFilter hides rows that fail the criteria; it does not delete them. Clear the filter to display them again, and save a workbook copy before running macros because Ctrl+Z cannot undo macro actions.
Related
- Excel Text to Columns and Rows: How to Split Data
- Excel Text to Columns: Advanced Options and Uses
- Excel Text to Columns: Split Alt+Enter Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA InputBox Syntax: Arguments and ExamplesIn Excel VBA, the basic syntax is InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]).…
- VBA Subscript Error Handling: Causes, Checks, and FixesIn VBA, a subscript error usually means Error 9, “Subscript out of range”: code asked for an array…
- Excel VBA Offset: Move a Range by Rows and Columns in a MacroIn an Excel VBA macro, use the Offset property to refer to a cell or range a chosen…
- Add Libraries in Excel: Add-ins, VBA References & MoreTo add a library in Excel, first decide whether you mean an Office add-in, an Excel add-in file,…
- Excel VBA Array Methods: Create, Resize, and SearchExcel VBA does not provide a single built-in “array methods” object: instead, it offers array functions and statements…
- Auto Macro Recorder in Excel: How to Record and Use ItExcel’s macro recorder captures supported actions as VBA code, letting you replay a routine instead of repeating it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.