VBA to delete rows based on multiple criteria in multiple columns

gli

Board Regular
Joined
Jun 8, 2006
Messages
96
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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?
 
Upvote 0
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.

Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0
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.
 
Upvote 0
Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

Did you try the macro of post # 3?
 
  • Like
Reactions: gli
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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