VBA -在列中查找多个字符串，然后删除行

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

下面的VBA代码非常好用，但是我想添加更多的字符串来删除

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

e。酒店、住宅、公寓等50元

Edit- Values located in C column

编辑-位于C列中的值

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

1 个解决方案

#1

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

上面的例子是只搜索一个单词。如果你想找到重复，那就用Findnext。

An Alternative to .Find

另一种选择;

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

对上面的数组使用Autofilter。看到这个链接。这会给你一个开始。

#1