Overview
Excel allows you to specify wildcards (* or ?) in AutoFilter to extract only rows that contain or do not contain specific strings. In this article, I will carefully explain the basics and applications of wildcard specifications using VBA implementation examples.
Prerequisites
| Item | Content |
| Excel | Microsoft 365 or 2016 and later |
| Data Range | Assumes a table including a header row is at F3:I100 |
| Macro Location | Standard Module |
Sample Code (VBA)
1. Example: Extract rows containing a specific string
Sub FilterContainsKeyword()
Dim tblRange As Range ' Filter target
Set tblRange = Worksheets("Sheet1").Range("F3").CurrentRegion ' Range including header row
'--- Extract rows containing "Mori" in Column 4 (Col H) ---
tblRange.AutoFilter Field:=4, Criteria1:="=*Mori*"
End Sub
2. Example: Extract rows NOT containing a specific string
Sub FilterExcludesKeyword()
Dim tblRange As Range
Set tblRange = Worksheets("Sheet1").Range("F3").CurrentRegion
'--- Extract rows NOT containing "Mori" in Column 4 (Col H) ---
tblRange.AutoFilter Field:=4, Criteria1:="<>*Mori*"
End Sub
Key Points of the Code
| Line | Description |
| 3 | Retrieves the entire table (including headers) using CurrentRegion. Change the starting cell according to your actual data. |
| 6 | "*Mori*" is a wildcard expression that extracts only cells containing “Mori”. The * represents any string of characters. |
| 12 | Using <>*Mori* extracts only cells that do not contain “Mori”. The <> symbol represents the “Not Equal” condition. |
Application Examples
| Purpose | Implementation Hint |
| Extract multiple keywords with OR | Criteria1:=Array("*Mori*", "*Hayashi*"), Operator:=xlFilterValues |
| Composite extraction with AND | If you apply conditions to different columns in subsequent AutoFilter commands, it becomes an AND condition. |
| Case sensitivity | Execute Application.MatchCase = True before setting the filter (Set it back to False after processing). |
FAQ
Q. What is the difference between the wildcards ? and *?
A. * represents any string of 0 or more characters, while ? represents any single character. They offer different flexibility for partial matching.
Q. How do I copy only the visible cells after extraction?
A. You can handle only the visible cells by using tblRange.SpecialCells(xlCellTypeVisible).Copy.
Summary
By using AutoFilter settings with wildcards, you can immediately extract rows that contain or do not contain specific strings.
First, run the sample macro and confirm that the expected rows are displayed. You can apply this to various datasets simply by changing the keywords and column numbers, so please use it to improve your work efficiency.
