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

Please respond to post #8 . The code I gave you may be noticeably faster if you have many instances of cells conforming to one or more of the multiple criteria within your 20,000+ rows. BTW: the code I posted works fine for me, but not knowing your worksheet layout, the version below which replaces the usedrange with the specific columns of interest may be preferred.

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
    .Range("A:A,C:C,H:H").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

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!

I'm glad to help you. Thanks for the feedback.

 

[Danoz]

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

Hi Dante, hoping you can help me, I'm working in Excel 2007.

I am only trying to delete rows based on one criteria, being the existence of "False" in column A, which is determined successfully by the preceding script.

However when I try to amend your script to delete only the rows which have the word "False" in column A, all blank cells are also deleted. Can you please help me to update the script for this?

' Delete unneeded rows in selections
    Sheets("Selections").Select
    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("A10:O" & lr)
    For i = 1 To UBound(a)
        exists = False
        Select Case True
            Case a(i, 1) = "False": 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

Thanks for any help you can provide.

 

[Danoz]

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

I forgot to mention, rows 10 to 75 need to be included in the script

 

[DanteAmor]

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

I forgot to mention, rows 10 to 75 need to be included in the script

Try this

Sub Delete_Rows()
    Dim lr As Long, i As Long, a, r As Range
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set r = Range("A" & lr + 1)
    a = Range("A1:H" & lr)
    For i = 10 To UBound(a)
        If a(i, 1) = False And a(i, 1) <> "" Then
            Set r = Union(r, Range("A" & i))
        End If
    Next i
    r.EntireRow.Delete
    Set r = Nothing: Erase a
    Application.ScreenUpdating = True
End Sub

 

[Danoz]

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

Try this

Sub Delete_Rows()
    Dim lr As Long, i As Long, a, r As Range
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set r = Range("A" & lr + 1)
    a = Range("A1:H" & lr)
    For i = 10 To UBound(a)
        If a(i, 1) = False And a(i, 1) <> "" Then
            Set r = Union(r, Range("A" & i))
        End If
    Next i
    r.EntireRow.Delete
    Set r = Nothing: Erase a
    Application.ScreenUpdating = True
End Sub

Thanks Dante, works a charm. Sorry for not replying prior to now, i have been very busy.

 

[DanteAmor]

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

Thanks Dante, works a charm. Sorry for not replying prior to now, i have been very busy.

Dont worry. Im glad to help you. Thanks for the feedback.

 

[Danoz]

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

Dont worry. Im glad to help you. Thanks for the feedback.

No worries.

I've been trying to do a similar job but for columns.

I have a variable named value "MAX" which can be between 1 & 12. 1 to 12 is numbered in E3 to P3, A3 to D3 are blank. I want to delete the columns where the value is greater than MAX, but not if MAX =12, or to stop once the script hits 12 in P3.

Here's what I have so far, which is based on a 2009 q&a somewhere in the forum so apologies if it's outdated.

' Delete unneeded columns in selections  
Dim LR As Long, i As Long
Application.ScreenUpdating = False
If MAX = 12 Then End If        -------this is where it sticks-------
Else:[INDENT]LR = Range(Columns.Count & "3").End(xlLeft).Column[/INDENT]
[INDENT]For i = LR To 1 Step -1[/INDENT]
[INDENT]With Range(i & "3")[/INDENT]
[INDENT]If .Value = 12 Then End If[/INDENT]
[INDENT]Else:[/INDENT]
[INDENT=2]If .Value > MAX Then .Resize(7).EntireColumn.Delete
End With
Next i[/INDENT]
Application.ScreenUpdating = True

I'd love some help with this script also, p's and q's.

 

[DanteAmor]

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

No worries.

I've been trying to do a similar job but for columns.

I have a variable named value "MAX" which can be between 1 & 12. 1 to 12 is numbered in E3 to P3, A3 to D3 are blank. I want to delete the columns where the value is greater than MAX, but not if MAX =12, or to stop once the script hits 12 in P3.

Here's what I have so far, which is based on a 2009 q&a somewhere in the forum so apologies if it's outdated.

' Delete unneeded columns in selections  
Dim LR As Long, i As Long
Application.ScreenUpdating = False
If MAX = 12 Then End If        -------this is where it sticks-------
Else:[INDENT]LR = Range(Columns.Count & "3").End(xlLeft).Column[/INDENT]
[INDENT]For i = LR To 1 Step -1[/INDENT]
[INDENT]With Range(i & "3")[/INDENT]
[INDENT]If .Value = 12 Then End If[/INDENT]
[INDENT]Else:[/INDENT]
[INDENT=2]If .Value > MAX Then .Resize(7).EntireColumn.Delete
End With
Next i[/INDENT]
Application.ScreenUpdating = True

I'd love some help with this script also, p's and q's.

This is a different topic, in the previous one it is to delete rows, and this one is to delete columns. I suggest you create a new thread.