VBA Wildcards

Wildcards are used to match patterns in text. Wildcards can be used with the Like operator and with various functions and methods that support wildcards.

Note: The left brace "[" and right brace "]" characters cannot be used inside a charlist.

Like Operator

Wildcards can be used with the Like Operator to check if text matches a pattern.

Public Sub Example()

    Dim S As String
    S = "Hello, World!"

    'H* matches any text that starts in with H
    If S Like "H*" Then
        Debug.Print "True"
    Else
        Debug.Print "False"
    End If

End Sub

Find And Replace

Wildcards can be used with the Find and Replace methods of the Range object in Excel to search for patterns of text on spreadsheets. The below example shows how the * wildcard character can be used to find the last non-empty row on a spreadsheet.

Public Function GetLastRow(WS As Worksheet) As Long

    Dim Rng As Range

    Set Rng = WS.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

    If Not Rng Is Nothing Then GetLastRow = Rng.Row

End Function

Example Patterns