VBA to delete rows based on multiple criteria in multiple columns

gli

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

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,932
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
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
 

gli

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

MrExcel MVP
Joined
May 26, 2009
Messages
16,932
Office Version
2010
Platform
Windows
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
 

gli

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,932
Office Version
2010
Platform
Windows
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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,101,760
Messages
5,482,715
Members
407,359
Latest member
aaawww

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top