VBA to delete rows with multiple criteria in column A

Stevie T

New Member
Joined
Feb 9, 2019
Messages
5
Gday

Wonder if anyone of you kind folks could help? Looking for a VBA to delete rows which contain a
B, D, F, K, L, M, N,S O, X, Z as the first letter of the cell data in column A.
The below is what I have so far and works only if I remove the wildcard from after the letters in the array.

Sub DeleteRows()
Application.ScreenUpdating = False
Rows(1).EntireRow.Insert
Range("A1").Value = "Temp"
With Columns("A")
. AutoFilter Field:=1, Criteria1:=Array("B*", "D*", "F*", "K*", "L*", "M*", "N*", "O*", “S*”, "X*", "Z*"), Operator:=xlFilterValues
Rows("2:" & Rows.Count).EntireRow.Delete
.AutoFilter
End With
Rows(1).EntireRow.Delete
Application.ScreenUpdating = True
End Sub
<strike>
</strike>
Thanking you kindly
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Maybe this:

Code:
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1086927a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1086927-vba-delete-rows-multiple-criteria-column.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] va [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR], arr [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR], flag [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR]
arr = Array([COLOR=brown]"B"[/COLOR], [COLOR=brown]"D"[/COLOR], [COLOR=brown]"F"[/COLOR], [COLOR=brown]"K"[/COLOR], [COLOR=brown]"L"[/COLOR], [COLOR=brown]"M"[/COLOR], [COLOR=brown]"N"[/COLOR], [COLOR=brown]"O"[/COLOR], [COLOR=brown]"S"[/COLOR], [COLOR=brown]"X"[/COLOR], [COLOR=brown]"Z"[/COLOR])
[COLOR=Royalblue]Set[/COLOR] r = Range([COLOR=brown]"A1"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
va = r

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    flag = [COLOR=Royalblue]False[/COLOR]
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] x [COLOR=Royalblue]In[/COLOR] arr
            [COLOR=Royalblue]If[/COLOR] Left(va(i, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = x [COLOR=Royalblue]Then[/COLOR] flag = [COLOR=Royalblue]True[/COLOR]: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
    [COLOR=Royalblue]If[/COLOR] flag = [COLOR=Royalblue]True[/COLOR] [COLOR=Royalblue]Then[/COLOR] Cells(i, [COLOR=brown]"A"[/COLOR]) = [COLOR=brown]"#N/A"[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

r.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]
 
Upvote 0
Hi Akuini- Thanks, this works well and does what I need it to do but is really slow to process. Any way we can speed the process up. 2000 lines took almost 10 minutes to filter. Cheers
 
Upvote 0
Check whether this is any quicker :
Code:
Sub DeleteRows()
Dim ray, colA, dic As Object, a&, i
ray = Array("B", "D", "F", "K", "L", "M", "N", "O", "S", "X", "Z")
colA = Intersect(ActiveSheet.UsedRange, Columns("A")).Value2
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
For a = LBound(colA, 1) + 1 To UBound(colA, 1)
    For i = LBound(ray) To UBound(ray)
        If colA(a, 1) Like ray(i) & "*" Then
            dic.Add Key:=colA(a, 1), Item:=colA(a, 1)
            Exit For
        End If
    Next
Next
Application.ScreenUpdating = False
Rows(1).EntireRow.Insert
Range("A1").Value = "Temp"
Columns("A").AutoFilter Field:=1, Criteria1:=dic.keys, Operator:=xlFilterValues
Columns("A").SpecialCells(xlCellTypeVisible).EntireRow.Delete
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0
Hi Akuini
In the below vba, how would I be able to delete rows that also contain the letters TAX as in TAXC1945 and if the last letter in the text is an A or B. All will be in column A.

<tbody>
</tbody>

<tbody>
</tbody>

Code:
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Sub[/FONT][/COLOR]
[/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] a1086927a()
[/FONT][/COLOR][/LEFT]
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1086927-vba-delete-rows-multiple-criteria-column.html[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]
[/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Dim[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] i [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]As[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Long[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], r [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]As[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] Range
[/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Dim[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] va [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]As[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Variant[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], arr [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]As[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Variant[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], flag [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]As[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Boolean[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]
arr = Array([/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"B"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"D"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"F"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"K"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"L"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"M"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"N"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"O"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"S"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"X"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"Z"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console])
[/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Set[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] r = Range([/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"A1"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console], Cells(Rows.count, [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"A"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]).[/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]End[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console](xlUp))
va = r

[/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]For[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] i = [/FONT][/COLOR][/LEFT]
[COLOR=crimson][LEFT][COLOR=#DC143C][FONT=lucida console]2[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]To[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] UBound(va, [/FONT][/COLOR][/LEFT]
[COLOR=crimson][LEFT][COLOR=#DC143C][FONT=lucida console]1[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console])
    flag = [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]False[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]
        [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]For[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Each[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] x [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]In[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] arr
            [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]If[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] Left(va(i, [/FONT][/COLOR][/LEFT]
[COLOR=crimson][LEFT][COLOR=#DC143C][FONT=lucida console]1[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]), [/FONT][/COLOR][/LEFT]
[COLOR=crimson][LEFT][COLOR=#DC143C][FONT=lucida console]1[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]) = x [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Then[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] flag = [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]True[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]: [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Exit[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]For[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]
        [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Next[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]
    [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]If[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] flag = [/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]True[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Then[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console] Cells(i, [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"A"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]) = [/FONT][/COLOR][/LEFT]
[COLOR=brown][LEFT][COLOR=#A52A2A][FONT=lucida console]"#N/A"[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]
[/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Next[/FONT][/COLOR][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=lucida console]

r.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
[/FONT][/COLOR][/LEFT]
[COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]End[/FONT][/COLOR][/LEFT]
[/COLOR][COLOR=royalblue][LEFT][COLOR=#4169E1][FONT=lucida console]Sub[/FONT][/COLOR]
[/LEFT]
[/COLOR][/I]
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top