VBA: How to use like operator to a list of value?

Question

Here's part of my code. Is there any way to make it simple? Thank you.

For i = 2 To ws.Range("E1").CurrentRegion.Rows.Count

If ws.Cells(i, 4).Value Like ("*SSI*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Settlement instruction*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*delivery Instruction*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Request form*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.cells(i, 4).Value Like ("*Sales to onboarding*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Application*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Doc Check list*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Prime to Credit*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Prime to Legal*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Prime_Legal*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Prime_Credit*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*LEXIS*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Withdrawal Request*") Then ws.Cells(i, 4).EntireRow.Delete

Next i

Boolean condition = Like condition 1 or Like condition 2 or Like condition3 ...etc. If (condition) EntireRow.Delete. — Robert Harvey, Mar 13 '19 at 14:52
Note - the way you have this, and perhaps it's what you need, but If `Cells(2,4)` has `SSI`, then it'll delete the row. Then it will look **again** at `Cells(2,4)`, check if it has `Settlement instruction` in there, and if so, delete, then look **again** for the next ...etc. — BruceWayne, Mar 13 '19 at 14:54
there is also the `VBA.Filter()` function. [see here](https://www.tutorialspoint.com/vba/vba_filter_function.htm) — , Mar 13 '19 at 15:06

CLR · Answer 1 · 2019-03-13T16:06:52.837

2

There's lots of ways to do this, but here is one:

Firstly, when deleting rows, always start at the bottom of the range and move up - this prevents row skipping when deletions take place.

I've created an array by splitting the text out using commas. If your data possibly contains a comma, you'll need to change it.

Dim tmpAr As Variant
Dim test As Variant

Set ws = ActiveSheet
tmpAr = Split("SSI,Settlement instruction,delivery Instruction,Request form,Sales to onboarding,Application,Doc Check list,Prime to Credit,Prime to Legal,Prime_Legal,Prime_Credit,LEXIS,Withdrawal Request", ",")
For i = ws.Range("E1").CurrentRegion.Rows.Count To 2 Step -1
    For Each test In tmpAr
        If ws.Cells(i, 4).Value Like "*" & test & "*" Then
            ws.Cells(i, 4).EntireRow.Delete
            Exit For
        End If
    Next
Next i

edited Mar 13 '19 at 16:06

answered Mar 13 '19 at 15:03

CLR

11,284
1
11
29

How should I define tmpAr? Variant? string? Thank you. – 883km Mar 13 '19 at 15:28
and also test? I'm not familiar with range. Thank you – 883km Mar 13 '19 at 15:44
Declarations added in the answer. Create them as `Variant` and the `Split` and `For each..` does the rest. – CLR Mar 13 '19 at 16:07

score 1 · Answer 2 · answered Mar 13 '19 at 15:02

1

You could try something along these lines

Sub del()

Dim a As Variant
Dim s As Variant
Dim r As Range
Dim l As Long

a = Array("*abc*", "*def*", "efg*", "abcdef*hi")
Set r = Range("a1:a20")

For l = r.Rows.Count To 1 Step -1

    For Each s In a
        If r.Cells(l, 1).Value Like s Then
                Rows(l).EntireRow.Delete
                Exit For
        End If
    Next s

Next l

End Sub

answered Mar 13 '19 at 15:02

Nathan_Sav

8,466
2
13
20

You could use a range in excel to populate the array `a` also, and use the `r` range to populate the start and end of the loops. – Nathan_Sav Mar 13 '19 at 15:05

Daniel · Answer 3 · 2019-03-14T09:21:46.157

Please note:

When row is deleted, cells will shift up so row below will effectively have the same row number, which is why I used do loop instead with i = i - 1 after deleting row /e: yes I could have used step -1 or write it differently but wanted to show something different than other answers
There's no need to use like operator as well, it is not supported in vbs so if you ever decide to learn vbs, it's a good practise to avoid it

Here's my approach, you can keep adding more keywords to the array, or create collection instead:

MyArr = Array("SSI", "Settlement instruction", "delivery Instruction", "Request form", "Sales to onboarding", "Application", "Doc Check list", "Prime to Credit", "Prime to Legal", "Prime_Legal", "Prime_Credit", "LEXIS", "Withdrawal Request")

LastRow = ws.Range("E1").CurrentRegion.Rows.count
i = 1
Do Until i > LastRow
    i = i + 1
    cVal = ws.Cells(i, 4).Value
    For Each ma In MyArr
        If InStr(1, cVal, ma) > 0 Then
            ws.Cells(i, 4).EntireRow.Delete
            i = i - 1 'cells below will shift up, so next row will have the same row number
            Exit For
        End If
    Next
Loop

score 1 · Answer 4 · answered Mar 13 '19 at 15:10

run the loop backwards
avoid recalculations
only delete once

For i = ws.Range("E1").CurrentRegion.Rows.Count To 2 Step -1
    DR = False
    Set r = ws.Cells(i, 4)
    s = r.Value
    If s Like ("*SSI*") Then DR = True
    If s Like ("*Settlement instruction*") Then DR = True
    If s Like ("*delivery Instruction*") Then DR = True
    If s Like ("*Request form*") Then DR = True
    If s Like ("*Sales to onboarding*") Then DR = True
    If s Like ("*Application*") Then DR = True
    If s Like ("*Doc Check list*") Then DR = True
    If s Like ("*Prime to Credit*") Then DR = True
    If s Like ("*Prime to Legal*") Then DR = True
    If s Like ("*Prime_Legal*") Then DR = True
    If s Like ("*Prime_Credit*") Then DR = True
    If s Like ("*LEXIS*") Then DR = True
    If s Like ("*Withdrawal Request*") Then DR = True
    If DR Then ws.Cells(i, 4).EntireRow.Delete
Next i

VBA: How to use like operator to a list of value?

4 Answers4

Linked