Delete rows if cell contains certain text

Question

I want to perform this functions in this order:
1. Activate auto-filter function
2. In column M, tick 'deposit reversed' and 'blank'
3. Delete entire rows for cell that contain 'AQ*', 'AI*', 'BG' in column C. (NOTE: * represents numbers after the alphabets)

I have tried macro recording using Auto Filter but it only delete rows up to a specified range (which may differ if I use other set of data). VBA as per below.

Is there any EASIER/BETTER way to perform this?

Appreciate your help!

Sub Macro4()
'
' Macro4 Macro
'

'
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$46437").AutoFilter Field:=13, Criteria1:= _
    "=Deposit Reversed", Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$N$46437").AutoFilter Field:=3, Criteria1:=Array( _
    "AQ", "AQ01E166N", "AQ01E294N", "AQ01E316N", "AQ01E373N"), Operator:= _
    xlFilterValues
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$N$46017").AutoFilter Field:=3, Criteria1:=Array( _
    "AI", "AI04_MMRASHI_TWT", "AI04E230N", "AI04E269N", "AI04E323N"), Operator:= _
    xlFilterValues
ActiveWindow.SmallScroll Down:=-6
Rows("10236:10236").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$N$45998").AutoFilter Field:=3, Criteria1:=Array( _
    "BG", "BG01A004", "BG01H082", "BG01H106N"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-3
Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

End Sub

BG(continued by numbers - hence, I used *). Same goes to other search criteria — Siti
@Jeeped: It may be easier if I use excel formula first in column A (ie=LEFT(C2,2). If this is the case, what is the best VBA for the whole process? Search criteria: AQ, AI, BG — Siti
perhaps codereview.stackexchange.com would give you more feedback — Toby Allen

Unknown Unknown · Accepted Answer · 2015-02-28T08:54:54

Put the wildcarded filter criteria into an array and cycle through them. You won't find a faster method of removing the rows and it is safe as long as you check for the existence of rows to delete before deleting them.

    Dim c As Long, vCRITC As Variant
    vCRITC = Array("AQ*", "AI*", "BG*")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    With Sheets("Sheet7").Cells(1, 1).CurrentRegion
        If .AutoFilter Then .AutoFilter
        With .Resize(.Rows.Count, Columns("A:N").Count)
            .AutoFilter Field:=13, Criteria1:="=Deposit Reversed", Operator:=xlOr, Criteria2:="="
            For c = LBound(vCRITC) To UBound(vCRITC)
                .AutoFilter Field:=3, Criteria1:=Chr(61) & vCRITC(c)
                With .Offset(1, 0)
                    If CBool(Application.Subtotal(103, .Columns(3))) Then _
                        .Rows.Delete
                End With
                .AutoFilter Field:=3
            Next c
        End With
        .AutoFilter
    End With
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

It seems there was a typo in your original question that did not include the asterisk wildcard for BG. That may have to do with the composition editor using asterisks to note italics. This first sets the criteria on column M then adds (and removes) each wildcard criteria to column C, deleting rows that it finds.

Delete rows if cell contains certain text

2 Answers