VBA - Delete rows based on specific criteria

[spencerp237]

I have a previously asked a similar question to this here:

https://www.mrexcel.com/forum/excel...ontains-certain-text-specific-range-data.html

But never really got a resolution and it has expanded a bit.

I have a sheet with about 12000 rows of data. I will have somewhere between 4-6 criteria where if met, the rows would need to be deleted. I know I could write For loops for them but even doing that for 2 criteria takes a ridiculously long time to run. I am most interested in learning about faster ways of sorting through data like this for specific things.

Some examples of the criteria I have are:
1. Value in Column V is < 200
2. Value in Column O is in the range A2:A on Sheet8
3. Value in Column X is "Exclude"
4. Value in Column Y is "Exclude"
5. Value in Column E <> Blank

If any of these are met, I would need the entire row deleted

Thanks,

 

[Fluff]

Hia
How about something like this

Sub DelRw()

    Dim NxtCol As Long
    Dim Usdrws As Long
    Dim Rng As Range
    
Application.ScreenUpdating = False

    NxtCol = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Column
    Usdrws = Range("A" & Rows.Count).End(xlUp).Row
    
    Range(Cells(2, NxtCol), Cells(Usdrws, NxtCol)).Formula = "=IF(OR(V2>200,E2="""",X2=""Exclude"",Y2=""Exclude"",COUNTIF(Sheet8!A:A,O2)=1),""0"",""No"")"
    Set Rng = Range(Cells(2, 1), Cells(Usdrws, NxtCol))
    With Columns(NxtCol)
        .AutoFilter
        .AutoFilter field:=1, Criteria1:="<>No"
        On Error Resume Next
        Rng.SpecialCells(xlCellTypeVisible).EntireRow.[COLOR=#0000ff]Interior.ColorIndex = 3[/COLOR]
        On Error GoTo 0
        .AutoFilter
    End With
    Columns(NxtCol).Delete
    
Application.ScreenUpdating = True
    
End Sub

This will currently just highlight the rows rather than deleting them, so you can check that the correct rows are being affected.
If ok replace the part in blue with

.Delete

 

[spencerp237]

Fluff,

This is perfect. Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback