Yes. In Excel VBA, use AutoFilter with asterisks around the text in the criterion: Criteria1:="*invoice*" returns rows whose filtered column contains “invoice” anywhere in the cell. The asterisks are wildcards; set the Field number to the column’s position within the filtered range.
What AutoFilter means by “contains”
AutoFilter hides rows that do not meet a condition; it does not delete or change their values. For text matching, the asterisk (*) stands for any number of characters, including none. Therefore, *invoice* matches values such as “Invoice 104,” “paid invoice,” and “invoices pending,” because each contains that character sequence.
The criterion is applied to one field, meaning one column in the range being filtered. If the range starts in column A and column B is the second column of that range, use Field:=2. Field numbers are relative to the range, not worksheet column letters: if the range begins at column C, its first field is still 1.
AutoFilter is useful when you want to inspect matching records while keeping the full dataset intact. The filtered rows remain in the worksheet, and you can show them again by clearing the filter. A contains-text filter is different from searching for an exact cell value: the wildcards make the match partial rather than requiring the entire cell to equal the criterion.
How the VBA criterion works
The method is Range.AutoFilter. Its Field argument selects a column within the range, and Criteria1 supplies the condition. For example, the criterion *invoice* accepts any value with “invoice” somewhere in it. A criterion of invoice* instead matches values that start with “invoice,” while *invoice matches values that end with it.
Text AutoFilter matching is generally not case-sensitive, so a criterion for “invoice” also matches “Invoice” or “INVOICE.” The asterisk can match spaces and punctuation as well as letters. If you need stricter conditions—such as case-sensitive matching, a regular expression, or a condition involving several columns—AutoFilter may not be the right tool on its own.
A question mark (?) represents one character. For instance, INV-??? can match “INV-204” but not “INV-24.” The tilde (~) escapes a wildcard when you need to match it literally: use ~* for an asterisk character or ~? for a question mark character. Wildcard interpretation is a common reason an apparently exact criterion returns unexpected rows.
Apply a contains filter in VBA
In this example, the data has headers in row 1, records in rows 2 through 100, and the target text is in the second column of A:D. The macro applies the filter to that column:
Sub FilterRowsContainingText()
Dim ws As Worksheet
Dim dataRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:D100")
' Keep rows where the second field contains "invoice".
dataRange.AutoFilter Field:=2, Criteria1:="*invoice*"
End Sub
Change the worksheet name, range, field number, and search text to fit the workbook. The range should include the header row and all rows you intend to filter. In this sample, Field:=2 refers to column B because A:D is the range. If the target is column D, use Field:=4.
To use a variable for the search term, build the wildcard criterion around it. For example, with a string variable named searchText, the criterion expression is "*" & searchText & "*". If the input itself may include wildcard characters, escape them first; otherwise, an asterisk or question mark in the input will act as a wildcard rather than a literal character.
Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, even though this particular macro filters rows rather than editing cell contents.
Choose the correct range and field
A reliable filter depends on a well-defined data range. The first row should contain column headings, and the records should form a contiguous block with consistent columns. Blank rows or separate notes inside the block can make Excel interpret the data boundary differently from what you intended. If the dataset grows, a fixed range such as A1:D100 will not automatically include new rows; update it or use an Excel table.
Count the field from the left edge of the range, not from the worksheet. These examples illustrate the distinction:
- For
A1:D100, column A is field 1 and column D is field 4. - For
C1:F100, column C is field 1 and column F is field 4. - For a table range, the field number is relative to the table’s leftmost column.
When the data is stored as an Excel table, adding records at the bottom generally expands the table automatically, which makes it less likely that the filter range will omit new rows. You can still call AutoFilter on the table’s range, or use the table’s existing filter controls. Check that the table has a header row and that the field number points to the intended table column.
Filter for more than one condition
A single contains condition is usually enough for a quick text search. You can also combine conditions for the same field. To keep rows where a cell contains either “invoice” or “receipt,” AutoFilter can use two criteria with an OR operator:
dataRange.AutoFilter Field:=2, _
Criteria1:="*invoice*", _
Operator:=xlOr, _
Criteria2:="*receipt*"
To require both terms in the same cell, use Operator:=xlAnd with the two wildcard criteria. That means a cell must contain “urgent” and “email,” in either order. It does not mean that one term can appear in one column and the other in another; this call applies both criteria to the same field.
Filters on separate fields can be combined too. Apply a condition to one field and then apply a condition to another field in the same filtered range. Excel will show records meeting the active conditions together. Be aware that applying a new criterion to a field replaces that field’s prior criterion; it does not add a second independent filter to that same field unless the call specifies the two-criteria logic.
Common problems and how to fix them
- No rows appear: Check spelling, the selected field, and whether the text is actually in the chosen column. Also verify that the filter range includes the records and that the criterion is not more restrictive than intended.
- The wrong column is filtered: Recalculate
Fieldfrom the left edge of the AutoFilter range. It is not necessarily the worksheet column number. - Some expected values are missing: Look for leading or trailing spaces, unusual punctuation, nonprinting characters, or a different spelling. A contains criterion ignores surrounding characters, but it still needs the search sequence to match.
- The text includes an asterisk or question mark: Those characters have wildcard meanings. Escape a literal wildcard with a tilde in the criterion, such as
~*. - The macro errors or filters the wrong block: Confirm the worksheet name, range address, header row, and active filter range. A range with no headers or an unintended blank row can produce confusing results.
- New rows are not included: A fixed range does not expand just because records were added below it. Expand the range or use a table that grows with appended data.
AutoFilter is intended for filtering tabular data, not for finding text anywhere on a worksheet. If the target text might occur in several different columns, either apply appropriate conditions to each relevant field or use a different method to search the data. Hidden rows can also make a filtered result look incomplete; clear other filters and check whether rows were hidden manually.
VBA versus the Excel filter menu
For a one-off search, the built-in filter menu is often faster than writing a macro. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > Filter. Open the arrow on the target column, choose Text Filters > Contains, and enter the text. This produces a similar result without requiring VBA.
VBA is useful when the filter must be repeated, triggered by another procedure, or applied consistently to multiple workbooks. It can also accept a search term from a cell or a form. The trade-off is that macros require appropriate macro settings and a macro-enabled workbook format, such as .xlsm, if you need to retain the VBA code.
Excel for Mac includes desktop VBA, though available keyboard shortcuts and some interface details can differ from Windows. Excel for the web can open many macro-enabled workbooks, but it cannot create, run, or edit VBA macros; use desktop Excel for those tasks. The normal column-filter interface remains an option when VBA is unavailable.
Frequently Asked Questions
What is the VBA AutoFilter syntax for “contains”?
Use Range.AutoFilter Field:=n, Criteria1:="*text*", replacing the range, field number, and text. The asterisks allow any characters before and after the search text.
Is AutoFilter contains matching case-sensitive?
No, standard Excel AutoFilter text criteria are not case-sensitive. If the distinction between uppercase and lowercase matters, use a different approach, such as code that explicitly compares text with a case-sensitive option.
How do I filter cells that start or end with text?
Use text* to match values that start with the text, or *text to match values that end with it. Use *text* for a match anywhere in the cell.
Can AutoFilter find text in any column?
One AutoFilter call checks the field you specify. To search several columns, apply appropriate criteria to the relevant fields or use a different search method; a single field criterion does not scan the entire row automatically.
How do I remove the filter and show all rows?
Use the column’s filter menu to clear its condition, or use Data > Clear to clear filters on the worksheet. In VBA, Worksheet.ShowAllData shows the rows when the worksheet is currently filtered; check the filter state before calling it to avoid errors.
When another method is a better fit
AutoFilter is a good choice for interactive, reversible filtering of a rectangular list. For a reusable output that extracts matching records elsewhere, a worksheet formula or Power Query may be more suitable, depending on the Excel version and whether the result should update automatically. For a case-sensitive search or complex rules spread across multiple columns, custom VBA logic may provide more precise control than wildcard criteria.
Choose based on the desired result: use the filter menu for a quick manual check, VBA AutoFilter for repeatable row filtering, and an extraction or transformation method when you need a separate result set. Whatever method you use, keep clear headers and a consistent data layout; those basics make both filtering and later analysis more dependable.
Related
- Excel Text to Columns: Find It and Split Data Correctly
- 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 UserForm Coding: Build a Data-Entry FormExcel VBA UserForm coding means creating a custom dialog in Visual Basic for Applications and writing code that…
- Excel VBA AutoFilter Based on a Cell Value: ExamplesYes. In VBA, pass the value of a worksheet cell to the Criteria1 argument of Range.AutoFilter, for example:…
- VBA Split Function: How to Break Text into an ArrayIn VBA, use the Split function to divide a string wherever a specified delimiter appears; it returns the…
- VBA Excel Copy Entire Column: Code and TipsTo copy an entire column in Excel with VBA, use the column’s Copy method and specify the destination:…
- PowerPoint VBA InputBox Location and How to Position ItIn PowerPoint VBA, call the built-in InputBox function from a macro; its optional XPos and YPos arguments set…
- Excel VBA: Delete a Row If a Cell Is BlankYes. In Excel VBA, loop through the rows from the bottom upward and delete a row when the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.