VBA Macro to Delete Rows if Multiple Values Exist in Columns

[Excel Mitchell]

Hello, everyone.

I'm attempting to make a macro that will delete the row if specific multiple values exist anywhere in that row. I searched and found a formula which looks like it'll work. I've modified it but it's not working for me and gives me an error. Any assistance you can provide would be a big help.

Sub quickDelete()
    Application.ScreenUpdating = False
    Dim rngFind As Range
    Dim strValueToPick As String
    Dim rngLook As Range
    Dim strFirstAddress As String
    Dim topRow As Long
    Dim botRow As Long
    Dim rf As Range
    Dim rfa As String
    Set rngLook = Range("A1:AA500000" & Cells(Rows.Count, 1).End(xlUp).Row)
    strValueToPick = "A","B","C"
    With rngLook
        Set rngFind = .Find(strValueToPick, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Do
                Set rf = rngFind
                rfa = rngFind.Address
                rngFind.EntireRow.Delete
            Loop While Not rf Is Nothing And rfa <> strFirstAddress
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[NdNoviceHlp]

Not real sure what your trying to set your range to? This will remove the rows whenever an "A" or "B" or "C" occurs in column "A" of sheet 1. The range can be adjusted as needed. HTH. Dave

Option Explicit
Sub quickDelete()
Application.ScreenUpdating = False
Dim Lastrow As Double, RngLook As Range
Dim strValueToPick As Integer, RngFind As Range
Dim InputStr As String
Dim SplitStr As Variant
With Sheets("Sheet1")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set RngLook = .Range(.Cells(1, "A"), .Cells(Lastrow, "A"))
End With
InputStr = "A,B,C"
SplitStr = Split(InputStr, ",")
With RngLook
For strValueToPick = LBound(SplitStr) To UBound(SplitStr)
above:
Set RngFind = .Find(SplitStr(strValueToPick), LookIn:=xlValues, lookat:=xlWhole)
If Not RngFind Is Nothing Then
RngFind.EntireRow.Delete
GoTo above
End If
Next strValueToPick
End With
Application.ScreenUpdating = True
End Sub

 

[Excel Mitchell]

Thank you so much. This did work but it seems to take awhile (about 2 minutes to run). This spreadsheet is currently 80k rows and will continue to grow. Maybe I'm just thinking about this the wrong way. Are there any other faster methods? I read about using power filter but I'm not sure that it'll be any faster as this spreadsheet continues to scale in size.

 

[My Aswer Is This]

Can you please be more specific.

Look in what column or columns for what values.

 

[NdNoviceHlp]

80K rows and only 2 mins... sounds not bad. Deleting rows takes a long time... is it really necessary to delete the whole row? Dave