VBA - find multiple rows in a column and then delete rows
The following VBA code works fine, but I want to add more lines to delete
for example, hotel, house, apartment, etc. up to 50 values
Change the values located in column C
I have looked through the arrays but still cannot 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
+1
source to share
1 answer
Deleting lines in a loop can really slow down your code. Store them in a time range and then delete them in one go. Also store all the words you want to find in the array and then loop through to do the search.
Try this (not indexed).
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 searching for just one word. If you want to find duplicates use Findnext
Alternative .Find
Use Autofilter with the above array. See this link. This will give you a start.
+1
source to share