VBA to delete rows based on multiple criteria in multiple columns

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,941
Office Version
2010
Platform
Windows
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.
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
    .Range("A:A,C:C,H:H").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

DanteAmor

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

New Member
Joined
Jul 22, 2010
Messages
39
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?

Code:
' 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

New Member
Joined
Jul 22, 2010
Messages
39
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

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

Code:
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

New Member
Joined
Jul 22, 2010
Messages
39
Re: Looking for VBA to delete rows based on multiple criteria in multiple columns

Try this

Code:
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

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

New Member
Joined
Jul 22, 2010
Messages
39
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.

Code:
' 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

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

Code:
' 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,135
Messages
5,484,938
Members
407,475
Latest member
Dix_Fix

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