CODEWITHSUNDEEP
vbaexcel
pandzkilla
pandzkilla

Reputation: 15

How to add multiple criteria when deleting entire row?

I've searched already and I have no luck finding the right answer or creating my code:

This code will delete the entire row that contains "apple" in Col D.

How will I add criteria in strSearch? I want to add "banana" and "cat"?

Sub Delete_EntireRow_Values()
'
' Delete_EntireRow_Values Macro

Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim sFirstAddress As String

strSearch = "apple"

Set rDelete = Nothing

Application.ScreenUpdating = False

With Sheet1.Columns("D:D")
    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
    If Not rFind Is Nothing Then
        sFirstAddress = rFind.Address
        Do
            If rDelete Is Nothing Then
                Set rDelete = rFind
            Else
                Set rDelete = Application.Union(rDelete, rFind)
            End If
            Set rFind = .FindNext(rFind)
        Loop While Not rFind Is Nothing And rFind.Address <> sFirstAddress

        rDelete.EntireRow.Delete

    End If
End With
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 39

Answers (1)

user2140173
user2140173

Reputation:

A more efficient way would be to use an autofilter and pass an array of words to delete

Sub DeleteMatchingCriteria()
Application.ScreenUpdating = False
    Dim toDelete As Variant

    ' set the words to delete
    toDelete = Array("apple", "cat", "bannana")

    Dim colD As Range
    Set col = Sheet1.Range("D1:D" & Sheet1.Range("D" & Rows.Count).End(xlUp).Row)

    With col
        .AutoFilter Field:=1, Criteria1:=toDelete, Operator:=xlFilterValues
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    Sheet1.AutoFilterMode = False
End Sub

Upvotes: 1

Related Questions