CODEWITHSUNDEEP
excelvbawildcardvb-like-operator
883km
883km

Reputation: 27

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

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

Upvotes: 2

Views: 1872

Answers (4)

Daniel
Daniel

Reputation: 954

Please note:

  1. 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
  2. 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

Upvotes: 1

CLR
CLR

Reputation: 12279

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

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

  1. run the loop backwards
  2. avoid recalculations
  3. 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

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

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

Upvotes: 1

Related Questions