VBA : HOW TO USE NOT LIKE

This tutorial explains how to use NOT LIKE in VBA.

The NOT LIKE is used to check if a string does not match a specified pattern. There is no direct NOT LIKE operator in VBA. We can combine the Like operator with Not statement to negate the LIKE condition.

You can download the following dataset to practice.

Syntax of NOT LIKE

If Not variable Like pattern Then
    ' Code to execute if the variable does not match the pattern
End If

The Variable is the string we want to compare. The pattern is the search pattern against which we compare the variable. The pattern can include wildcards.

Wildcards with the Like Operator

* (asterisk): It matches any number of characters (including zero).
? (question mark): It matches exactly one character.
# (hash): It matches exactly one numeric digit.
[ ] (brackets): It matches any one of the characters inside the brackets.

Let us consider a few examples to understand the practical applications of NOT LIKE.

1. Cities Without Letter "a"

The following macro can be used to highlight all the cities in column B of the dataset that do not contain the letter 'a' :

Sub without_a()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

        For Each Cell In ws.Range("B2:B" & lastRow)
    
        If Not Cell.Value Like "*a*" Then
            Cell.Interior.Color = vbRed
        End If
        Next Cell
End Sub

Press Run or F5 to obtain the following output.

2. First and Last Names Do Not Start with a Capital Letter

The following macro can be used to highlight all names that do not follow the pattern where both the first and last names begin with a capital letter :

Sub First_Last()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

        For Each Cell In ws.Range("A2:A" & lastRow)
    
        If Not Cell.Value Like "[A-Z]* [A-Z]*" Then
            Cell.Interior.Color = vbRed
        End If
        Next Cell
End Sub

Press Run or F5 to obtain the following output :

VBA : Using NOT LIKE along with Wildcards

3. Combining Multiple NOT LIKE Conditions

We can combine multiple NOT LIKE conditions using And or Or. The following macro can be used to highlight names that do not start with 'J' and whose corresponding dates do not end with '5' :

Sub multiple_condition()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")

        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If Not ws.Cells(i, 1).Value Like "J*" And Not ws.Cells(i, 3).Value Like "*5" Then
            ws.Cells(i, 3).Interior.Color = vbRed
             ws.Cells(i, 1).Interior.Color = vbRed
        End If
    Next i
End Sub

Press Run or F5 to obtain the following output :

VBA : Multiple NOT LIKE condition using AND or OR

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn