CODEWITHSUNDEEP
vbaexcel
Elixir
Elixir

Reputation: 303

VBA - find multiple strings in column then delete rows

The following VBA code works great however I want to add more strings to delete

e.g Hotel , Home, Flat etc up to 50 values

Edit- Values located in C column

I've looked into arrays but still can't find a solution 

Dim Find As Range
Application.ScreenUpdating = False
Set Find = .Range("C:C").Find(what:="House")
Do Until Find Is Nothing
    Find.EntireRow.Delete
    Set Find = .Range("C:C").FindNext
Loop

Upvotes: 1

Views: 4298

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Deleting the rows in a loop can really slow down your code. Store them in a temp range and then delete it in one go. Also store all the words that you want to find in an array and then loop through it to do a search.

Try this (Untested).

Sub Sample()
    Dim sString As String
    Dim MyAr
    Dim i As Long
    Dim delRange As Range, aCell As Range
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    '~~> Add more to the list here separated by "/"
    sString = "Hotel/Home/Flat"

    MyAr = Split(sString, "/")

    With ws
        For i = LBound(MyAr) To UBound(MyAr)

            Set aCell = .Columns(3).Find(What:=MyAr(i), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

            If Not aCell Is Nothing Then
                If delRange Is Nothing Then
                    Set delRange = .Rows(aCell.Row)
                Else
                    Set delRange = Union(delRange, .Rows(aCell.Row))
                End If
            End If
        Next i
    End With

    If Not delange Is Nothing Then delRange.Delete
End Sub

The above example is to search for only one word. If you want to find repeats then use Findnext

An Alternative to .Find

Use Autofilter with the array above. See this link. That will give you a start.

Upvotes: 1

Related Questions