VBA AutoFilter: Filter Text with Like Patterns

To filter rows in Excel VBA for text that is like a pattern, use the AutoFilter method with Excel’s wildcard characters in Criteria1—for example, Criteria1:="*east*" finds cells containing “east.” AutoFilter does not take VBA’s Like operator directly; its wildcards are a similar but separate pattern system.

What “Like” Means in AutoFilter

In VBA, Like is an operator used in a comparison, such as value Like "A*". AutoFilter is a worksheet filtering method, so you pass a text criterion to it rather than writing a Like expression. For text matching, Excel AutoFilter recognizes an asterisk (*) for zero or more characters and a question mark (?) for one character.

For example, *east* means “contains east,” east* means “begins with east,” and *east means “ends with east.” This is the practical equivalent of common VBA Like patterns, but the two systems should not be treated as interchangeable. VBA Like can also use character lists and ranges, such as [A-Z]; AutoFilter criteria do not provide the same general pattern syntax.

AutoFilter text matching is generally case-insensitive in Excel. A criterion of *east* will therefore normally match “East,” “EAST,” and “Northeast.” If the task requires case-sensitive comparisons or more elaborate rules, a helper column or a VBA loop may be more suitable than AutoFilter.

Set Up the Range and Field Correctly

The range passed to AutoFilter should include the header row and the data rows. The Field argument is a number counted from the left edge of that range—not the worksheet column number. If the filtered range is A1:D100, Field:=2 refers to column B. If the range starts in column C, Field:=2 refers to column D.

On Excel for Microsoft 365 for Windows, you can also set up a filter through the ribbon using Data > Filter. In VBA, the relevant method is Range.AutoFilter. The header text matters because Excel uses the first row of the range as the filter-header row. Including a blank row above the headers or omitting the headers can make the filter act on the wrong data.

This macro filters the second field of A1:D100 for cells containing “east.” Change the worksheet name, range, field number, and search text to fit the workbook. It escapes wildcard characters in the search term so a literal asterisk or question mark entered by a user is not accidentally interpreted as a pattern.

Sub FilterRowsContainingText()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim searchText As String

    ' Save a copy of the workbook before running a macro.
    ' Macro actions cannot be undone with Ctrl+Z.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")
    searchText = "east"

    ' Escape Excel wildcard characters in the literal search text.
    searchText = Replace(searchText, "~", "~~")
    searchText = Replace(searchText, "*", "~*")
    searchText = Replace(searchText, "?", "~?")

    dataRange.AutoFilter Field:=2, Criteria1:="*" & searchText & "*"
End Sub

Choose the Right Wildcard Pattern

Putting asterisks before and after the search term is the usual way to find text anywhere in a cell. To match names, locations, product codes, or notes, the position of the wildcard changes what is included. A filter only hides rows that do not match; it does not remove or edit their contents.

*road* matches text with “road” anywhere in the cell, such as “Roadside” or “Old Road.”
North* matches values beginning with “North,” such as “North Shore.”
*Ltd matches values ending with “Ltd,” such as “Example Ltd.”
AB?12 matches a five-character value with any one character between “AB” and “12,” such as “ABX12.”
~* represents a literal asterisk, and ~? a literal question mark. The tilde is Excel’s wildcard escape character.

For a whole-cell text match, pass the text without surrounding asterisks, such as Criteria1:="North". If the criterion is assembled from user input, escape tilde first, then asterisk and question mark, as in the macro above. The order matters: escaping the tilde first prevents the newly inserted escape characters from being escaped again.

Use Criteria1 and Combine Conditions

AutoFilter’s Criteria1 argument holds the first criterion. You can build it from a variable with VBA concatenation: "*" & searchText & "*". That makes the filter reusable for different search terms. Keep in mind that special characters in the variable can still act as wildcards unless you escape them.

To match either of two text patterns in the same field, use the xlOr operator and provide Criteria2. For example, filtering a region field for values containing either “east” or “west” uses the equivalent criteria "*east*" and "*west*" with Operator:=xlOr. The Field number still refers to the field’s position within the filtered range.

AutoFilter can also apply two conditions to one field with xlAnd, such as values greater than one threshold and less than another. The criteria syntax depends on the type of data and the intended comparison. For a complex “contains this phrase but not that phrase” rule, or conditions spanning several columns, a helper column with a clear TRUE/FALSE result can be easier to understand and maintain than a long chain of filter arguments.

Use AutoFilter with an Excel Table

For a data set that grows over time, an Excel Table is often more dependable than a fixed address such as A1:D100. Tables expand as rows are added, keep filter controls with their headers, and reduce the chance that a macro will omit newly appended records. You can create a table from a selected data range with Insert > Table, making sure the “My table has headers” option reflects the data.

A table can be referenced in VBA through the worksheet’s ListObjects collection. The table’s DataBodyRange contains data rows but not the header row, so filtering is normally applied through the table’s AutoFilter or its range. Confirm that the table exists and that its field index corresponds to the table’s column order. This is different from using a worksheet column number, just as with a regular range.

When a macro is intended to work on a specific table, refer to it by its table name rather than guessing a last row. Table names are visible on the Table Design tab when the table is selected. If a workbook contains multiple tables or the table can be renamed, use the actual name in the code and handle a missing table deliberately instead of silently filtering another range.

Common Problems and Ways to Fix Them

A filter that appears to do nothing often has a range or data issue rather than a wildcard issue. Check that the header is included, the Field number points to the intended column, and the values really contain the text being searched. Leading or trailing spaces, imported nonbreaking spaces, and numbers stored as text can cause surprising results. A visible value may also be the result of a formula; AutoFilter filters the displayed cell values, not the formula’s text as VBA source code.

Wrong column filtered: Recount Field from the first column of the AutoFilter range, starting at 1.
Only part of the data is affected: Expand the range or use a table that grows with added rows.
No matching rows appear: Check spelling, spaces, data type, and whether the criterion needs wildcards on both sides.
Literal * or ? is being treated as a wildcard: Prefix it with a tilde in the AutoFilter criterion.
ShowAllData raises an error: Call it only when the sheet is actually filtered; a filter dropdown can exist even when no rows are hidden.

AutoFilter changes the visible state of the worksheet, so a macro can overwrite an existing filter selection on the range it targets. If preserving a user’s filter choices matters, plan for that explicitly rather than clearing filters automatically. Before running a macro, save a separate copy of the workbook: macro actions cannot be reversed with Ctrl+Z. Macros can be run in Excel for Mac too, although ribbon layout and macro-security prompts may differ; the VBA logic and AutoFilter criteria are broadly the same.

When AutoFilter Is Not the Best Match

AutoFilter is a good choice when a user needs to inspect matching rows in place, use filter dropdowns, or quickly focus on records without changing the source data. It is also convenient for a repeatable macro that applies a straightforward contains, begins-with, or ends-with test.

It is less suitable when the desired result must be copied to a new location, when conditions need case-sensitive matching, or when a pattern relies on VBA Like features that AutoFilter does not support. In those cases, a helper column can calculate whether each row qualifies, and that result can then be filtered. A VBA loop can also compare each value with Like and take a specific action on matches, but it requires more code and careful handling of hidden rows, errors, and data types.

For a one-off visual task, the Data > Filter controls may be simpler than a macro. For a repeated operation, VBA can make the same filtering step consistent. Choose based on whether the goal is interactive review, a reproducible filter, or a separate result set—not merely on whether the word “Like” appears in the requirement.

Frequently Asked Questions

Can I use VBA Like directly in AutoFilter?
No. Like is a VBA comparison operator, while AutoFilter receives criteria strings. Use AutoFilter wildcards such as *text* for contains matching, or use a VBA loop with Like when you need features AutoFilter does not support.

How do I filter a column for text that contains a word?
Include asterisks on both sides of the word in Criteria1, for example Criteria1:="*invoice*". This matches cells containing “invoice” anywhere in the value, subject to Excel’s usual case-insensitive text filtering.

How do I filter for a value that starts or ends with text?
Use a trailing asterisk for a beginning match, such as "North*", or a leading asterisk for an ending match, such as "*Ltd". These are AutoFilter criteria, not VBA Like expressions.

How do I search for an actual asterisk in a cell?
Escape the asterisk with a tilde in the criterion: ~*. For example, a criterion with wildcard characters around that literal symbol can be assembled as "*~**". If the search term comes from a variable, escape tilde, asterisk, and question mark before adding the surrounding wildcards.

Why does Field:=2 filter the wrong column?
Field counts columns within the range supplied to AutoFilter, beginning at the range’s left edge. In A1:D100, 2 means column B; in C1:F100, 2 means column D. It is not the worksheet’s absolute column number.

Can AutoFilter match text without regard to capitalization?
Excel AutoFilter text criteria are generally case-insensitive. If capitalization must be distinguished, use a helper calculation or a VBA comparison designed for case-sensitive matching rather than relying on a standard AutoFilter text criterion.

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.