VBA to delete rows based on multiple criteria in multiple columns

[gli]

Hello all,

I have a large spreadsheet that is only 8 columns wide but 20k+ rows deep. I am looking for code that will efficiently delete rows based on multiple criteria. Right now I am making the deletions by hand but am looking for a way to automate the process.

Below is the criteria that should cause a row to be deleted based on the column in question. Any of these criteria may or may not be present in the data.

Column A - any row with a cell that contains the following numbers: "998" and "999".
Column C - any row that contains the word "CLOSED". This would be any instance of the word "CLOSED", whether it is a stand alone word or not. On occasion it will have other text right up next to it, so the code would need to account for that and still delete the row.
Column H - any row with a cell that contains the following numbers: "28" and "34".

There are more numbers that need to be deleted, but I figured I would save you guys some keystrokes and just repeat the code as needed for the missing numbers once I know what the setup of the code will look like. Thank you for any help you can provide!

 

[JoeMo]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

For columns A & H, do you mean that BOTH numbers must be in the same cell, or should your AND be an OR?

 

[DanteAmor]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

Try this
I put some examples so you can add more numbers.

Sub Delete_Rows()
    Dim lr As Long, i As Long, a, r As Range, exists As Boolean
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    a = Range("A1:H" & lr)
    For i = 1 To UBound(a)
        exists = False
        Select Case True
            Case a(i, 1) = 998, a(i, 1) = 999: exists = True
            Case UCase(a(i, 3)) Like "*CLOSED*": exists = True
            Case a(i, 8) = 28: exists = True
            Case a(i, 8) = 34: exists = True
        End Select
        If exists Then
            If r Is Nothing Then
                Set r = Range("A" & i)
            Else
                Set r = Union(r, Range("A" & i))
            End If
        End If
    Next i
    r.EntireRow.Delete
    Set r = Nothing: Erase a
    Application.ScreenUpdating = True
End Sub

 

[gli]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

For columns A & H, do you mean that BOTH numbers must be in the same cell, or should your AND be an OR?[/Q]

Sorry, that should be an OR. So if column A has 998 or 999, delete. Same with column H.

 

[JoeMo]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

For columns A & H, do you mean that BOTH numbers must be in the same cell, or should your AND be an OR?[/Q]

Sorry, that should be an OR. So if column A has 998 or 999, delete. Same with column H.

Try this on a copy of your worksheet. Be aware that the presence of any one of the criteria, as part of or the entirety of a cell will cause deletion, and there's no undoing it. For example, a row having a cell containing the string ABC03998654DEF will be deleted.

Sub gli()
Application.ScreenUpdating = False
With ActiveSheet
    .Columns("A").Replace what:="*998*", replacement:="#N/A", Lookat:=xlWhole
    .Columns("A").Replace what:="*999*", replacement:="#N/A", Lookat:=xlWhole
    .Columns("C").Replace what:="*CLOSED*", replacement:="#N/A", Lookat:=xlWhole
    .Columns("H").Replace what:="*28*", replacement:="#N/A", Lookat:=xlWhole
    .Columns("H").Replace what:="*34*", replacement:="#N/A", Lookat:=xlWhole
    On Error Resume Next
    .UsedRange.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

 

[gli]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

JoeMo, when I use your code the affected cells with matching criteria are being replaced with "#N/A", but the rows aren't being deleted.

 

[DanteAmor]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

Did you try the macro of post # 3?

 

[JoeMo]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

JoeMo, when I use your code the affected cells with matching criteria are being replaced with "#N/A", but the rows aren't being deleted.

Comment out the On Error Resume Next line and try again - is there an error on the specialcells line?

 

[gli]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

Hi DanteAmor, I tried your code just now and it appears to work as needed. I'm looking closer at the data to make sure, but all indications are that it did the job nicely and accurately. Thanks!

 

[gli]

Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

Hi Joe, thank you for your efforts. Since the other code worked I'm going to use it, but I greatly appreciate your help and I thank you for your time.