Multiple case statements VBA

[sjk1193]

I need to search column E for specific values and if that row has them then it will not delete the row

The problem is that I need to search it based on if it starts with something. So in the example below if cell E98 started with "AG____ " then that row would not be deleted

Dim iLastRow As Long
    Dim i As Long
    iLastRow = Cells(Rows.Count, "E").End(xlUp).Row
        For i = iLastRow To 2 Step -1
            Select Case Cells(i, "E").Value
                Case "AG*"
                Case "Alpinvest*"
                Case "Avenue Asia*"
                Case Else
                    Rows(i).Delete
            End Select
        Next i

 

[Fluff]

Try it like

   Dim iLastRow As Long
   Dim i As Long
   iLastRow = Cells(Rows.Count, "E").End(xlUp).Row
   For i = iLastRow To 2 Step -1
      Select Case False
      Case Left(Cells(i, 5), 2) = "AG", Left(Cells(i, 5), 9) = "Alpinvest"
      Case Else
      Rows(i).Delete
      End Select
   Next i

 

[steve the fish]

You could do it like this:

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "E").End(xlUp).Row

For i = iLastRow To 2 Step -1
    Select Case True
        Case UCase(Cells(i, "E").Value) Like "AG*" Or _
            UCase(Cells(i, "E").Value) Like "ALPINVEST*" Or _
            UCase(Cells(i, "E").Value) Like "AVENUE ASIA*"
        Case Else
            Rows(i).Delete
    End Select
Next i

 

[sjk1193]

m

 

[sjk1193]

I have a lot of cases and am getting the "Too many line continuations" message, would there be another way?​

​

 

[Akuini]

I have a lot of cases and am getting the "Too many line continuations" message, would there be another way?​

Hi, sjk1193
Since you have a lot of cases then you may want to create a list of the words say in sheet2 starting from A1.
Try this:

[B][COLOR=Royalblue]Sub[/COLOR][/B] a1079004a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1079004-multiple-case-statements-vba.html[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], j [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] tx [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] flag [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Boolean[/COLOR][/B]

[B][COLOR=Royalblue]Dim[/COLOR][/B] iLastRow [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
Application.ScreenUpdating = [B][COLOR=Royalblue]False[/COLOR][/B]
   
   [I][COLOR=seagreen]'get the list to an array[/COLOR][/I]
   [B][COLOR=Royalblue]With[/COLOR][/B] Sheets([COLOR=brown]"sheet2"[/COLOR])
    va = .Range([COLOR=brown]"A1"[/COLOR], .Cells(.Rows.count, [COLOR=brown]"A"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp)).Value
   [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]
    
    iLastRow = Cells(Rows.count, [COLOR=brown]"E"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).row
        [B][COLOR=Royalblue]For[/COLOR][/B] i = iLastRow [B][COLOR=Royalblue]To[/COLOR][/B] [COLOR=crimson]2[/COLOR] [B][COLOR=Royalblue]Step[/COLOR][/B] -[COLOR=crimson]1[/COLOR]
            flag = [B][COLOR=Royalblue]False[/COLOR][/B]
            tx = Cells(i, [COLOR=brown]"E"[/COLOR]).Value
            
            [B][COLOR=Royalblue]For[/COLOR][/B] j = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR])
            [B][COLOR=Royalblue]If[/COLOR][/B] InStr([COLOR=crimson]1[/COLOR], tx, va(j, [COLOR=crimson]1[/COLOR]), vbTextCompare) = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B] flag = [B][COLOR=Royalblue]True[/COLOR][/B]: [B][COLOR=Royalblue]Exit[/COLOR][/B] [B][COLOR=Royalblue]For[/COLOR][/B]
            [B][COLOR=Royalblue]Next[/COLOR][/B]
            
            [B][COLOR=Royalblue]If[/COLOR][/B] flag = [B][COLOR=Royalblue]False[/COLOR][/B] [B][COLOR=Royalblue]Then[/COLOR][/B] Rows(i).Delete
        [B][COLOR=Royalblue]Next[/COLOR][/B] i
Application.ScreenUpdating = [B][COLOR=Royalblue]True[/COLOR][/B]
        
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

Note: if your data is big then we may want to try another method to delete the rows all at once so it would be faster.

 

[pgc01]

Hi

Another option:

Dim iLastRow As Long
Dim i As Long
Dim vArray As Variant

vArray = Array("AG*", "Alpinvest*", "Avenue Asia*")
iLastRow = Cells(Rows.Count, "E").End(xlUp).Row

For i = iLastRow To 2 Step -1
    If Application.Sum(Application.CountIf(Cells(i, "E"), vArray)) = 0 Then _
        Rows(i).Delete
Next i