Excel VBA Filter Rows Containing Specific Text

VBA & macros · Updated November 19, 2026

To show only rows whose cells contain particular text, use Excel VBA’s AutoFilter method with a wildcard criterion such as *urgent*. For example, applying Criteria1:="*urgent*" to the column containing the text filters the list to matching rows without deleting the others.

What a contains-text filter does

Excel’s AutoFilter hides rows that do not meet the selected criteria and leaves matching rows visible. The data remains in the worksheet: clearing the filter makes the hidden rows visible again. This is useful for reviewing a subset of a list, such as orders whose notes mention “urgent,” without moving or removing records.

A filter range should include the header row and the data columns you want to filter. In an AutoFilter call, Field is the column’s position within that range, not its worksheet column number. If the range begins in column A, field 2 means column B; if the range begins in column C, field 2 means column D.

The asterisk is a wildcard meaning “any number of characters.” Putting one before and after a word finds that text anywhere in a cell. Thus, *urgent* can match “urgent,” “urgent review,” or “not urgent.” It is a substring search, not a test that the cell equals the search term.

Run a basic VBA filter

This example assumes a worksheet named Data, headers in row 1, data in columns A through D, and the text to search for in column B. It finds the last used row in those columns, then filters the second field of the range for cells containing “urgent.” Change the worksheet name, range, or search text to suit your workbook.

Option Explicit

Sub FilterRowsContainingText()
    Dim ws As Worksheet
    Dim filterRange As Range
    Dim lastCell As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last used row in the data columns.
    Set lastCell = ws.Range("A:D").Find(What:="*", _
        After:=ws.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If lastCell Is Nothing Then Exit Sub
    lastRow = lastCell.Row
    If lastRow < 2 Then Exit Sub

    Set filterRange = ws.Range("A1:D" & lastRow)

    ' Clear an existing filter result before applying this one.
    If ws.FilterMode Then ws.ShowAllData

    filterRange.AutoFilter Field:=2, Criteria1:="*urgent*"
End Sub

The Find call searches formulas as well as entered values, so a formula cell is included even when its displayed result is text. The code assumes row 1 is the header and that the relevant table occupies columns A:D. If your data has different columns, revise both the searched range and the field number. Before running a macro, save a copy of the workbook: VBA actions cannot be undone with Ctrl+Z.

Change the search term and target column

For a different column, change Field:=2 to the field’s position inside the filter range. For example, with A1:D100, column D is field 4. With a range of C1:F100, column D is field 2. Getting this relative position right is one of the most common fixes when a macro filters the wrong column.

To use a fixed search term, replace urgent in the criteria with the word or phrase you want to find. For a reusable macro that asks for text, declare a string variable and build the criterion from it. Escape wildcard characters first if the user’s input should be interpreted literally rather than as a pattern:

Dim searchText As String

searchText = InputBox("Text to find:")
If Len(searchText) = 0 Then Exit Sub

' Treat wildcard symbols in the input as literal characters.
searchText = Replace(searchText, "~", "~~")
searchText = Replace(searchText, "*", "~*")
searchText = Replace(searchText, "?", "~?")
filterRange.AutoFilter Field:=2, Criteria1:="*" & searchText & "*"

The tilde is Excel’s escape character for wildcard criteria. Doubling any entered tilde before escaping asterisks and question marks preserves it as a literal character. If users should be able to enter wildcard patterns intentionally, do not escape those characters.

Choose the right wildcard and matching behavior

AutoFilter criteria use wildcard patterns for text. An asterisk matches any sequence of characters, while a question mark matches one character. For instance, *east* finds “Northeast” and “east side,” while CA-?? can match a value such as “CA-12” when the two final characters can be anything.

  • Use *word* to find a word or phrase anywhere in a cell.
  • Use word* when the cell must start with the text.
  • Use *word when the cell must end with the text.
  • Escape a literal asterisk as ~* and a literal question mark as ~?.

Text filtering through AutoFilter is generally not case-sensitive, so a search for “urgent” also finds “Urgent.” If case-sensitive matching is essential, a worksheet helper formula or a VBA loop that compares text with an explicit binary comparison may be more appropriate. AutoFilter is convenient for ordinary lists, but it is not a general-purpose text-search engine for complicated rules.

Filter for more than one condition

To match either of two alternatives in the same column, supply two criteria and the OR operator. For example, this filters the second field for cells containing “urgent” or “review”:

filterRange.AutoFilter Field:=2, _
    Criteria1:="*urgent*", Operator:=xlOr, Criteria2:="*review*"

To apply conditions in different columns, call AutoFilter for each field on the same range. Excel combines filters on different fields, so a row must meet both column conditions. For example, filtering field 2 for a note and field 4 for a status can narrow the result to rows that satisfy both. Be mindful that applying a new criterion to a field replaces that field’s previous criterion; it does not create another independent filter on the same column.

If you need a complicated mixture of AND and OR rules, or want to return matching rows somewhere else rather than hide nonmatching ones, consider a helper column or a VBA loop that checks each row. This can be easier to inspect than a long set of AutoFilter criteria.

Common problems and practical fixes

  • The wrong column is filtered: Recalculate Field from the left edge of the filter range. It is not always the same as the worksheet column number.
  • No rows appear: Check spelling, the selected range, the header row, and whether the cells contain the expected text. A filter cannot find a value outside the range passed to AutoFilter.
  • The macro reports an error on ShowAllData: That method should only be called when a filter is actively hiding rows. The example checks ws.FilterMode first. A worksheet may have filter dropdowns without currently having filtered results.
  • The data range is incomplete: A last-row calculation based only on column A fails if column A has blanks below other data. The example searches A:D for the last used cell, but you should adjust those columns to match the actual table.
  • Text seems not to match: Check for unexpected spaces, nonprinting characters, or values that are stored differently than they appear. A formula result may also differ from the visible text you expect.

AutoFilter works best with a rectangular list: one header row, one record per row, and no merged cells in the table. Blank rows inside a list can make it harder to select a consistent range, so use an explicit range or an Excel table when the data grows regularly.

Clear a filter and preserve the data

To show all rows again while keeping the filter controls, use ws.ShowAllData when ws.FilterMode is true. To remove the dropdown arrows as well, use filterRange.AutoFilter to toggle the filter for that range, taking care not to toggle it off unexpectedly if the macro may be run repeatedly. The Data tab’s Filter command can also turn worksheet filtering on or off manually.

On Microsoft 365 for Windows, use Data > Filter to add or toggle filter dropdowns; the dropdown on a column offers Text Filters > Contains for a manual contains search. Excel for Mac has filtering tools on the Data tab as well, though some dialog details and shortcuts can differ. Excel for the web can filter a worksheet manually, but it does not create or run VBA macros; use desktop Excel for the VBA code.

Save the file in a macro-enabled format such as .xlsm if it must retain VBA. Excel may require the user to enable macros, depending on the workbook’s source and security settings. Only run macros from sources you trust.

When to use a table or another approach

If the list is likely to gain rows, converting it to an Excel table with Insert > Table gives the data a structured range that expands as records are added. A macro can then filter the table’s range rather than relying on fixed row limits. This is often easier to maintain, provided the table has a header row and the macro uses the correct table and field.

For a one-time search, the built-in dropdown’s Text Filters > Contains may be simpler than writing VBA. For a repeatable process, a macro is useful when it must apply the same filter after a button click or as part of a larger workflow. A helper column is a better fit when the rule needs case-sensitive comparisons, several custom conditions, or a visible TRUE/FALSE result that can be audited.

Frequently Asked Questions

Does the VBA filter delete rows that do not match?
No. AutoFilter hides nonmatching rows; it does not delete them. Clear the filter to display the full list again.

Is the contains-text filter case-sensitive?
AutoFilter text criteria are generally case-insensitive. If capitalization must distinguish matches, use a helper formula or custom VBA comparison instead.

How do I find text in a column other than column B?
Change the Field value to that column’s position within the range. For a range starting in A, column C is field 3; for a range starting in C, column D is field 2.

Can I search for an asterisk that is actually in the cell?
Yes. In an AutoFilter criterion, prefix the literal asterisk with a tilde, as in ~*. For a literal question mark, use ~?.

Can I run this VBA 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 macro; the web version can still apply ordinary worksheet filters.

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.