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.
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.
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
.
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.
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 :
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 :
Read MoreListenData