VBA - 在列中找到多个字符串，然后删除行 [英] VBA - find multiple strings in column then delete rows

问题描述

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

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

，例如酒店，家庭，平面等多达50个值

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

编辑 - 位于C列的值

Edit- Values located in C column

我查看了数组，但仍然找不到解决方案b
$ b

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

推荐答案

删除循环中的行可以减慢代码的速度。将它们存储在温度范围内，然后一次删除。还要存储你想在数组中找到的所有单词，然后循环遍历它进行搜索。

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.

尝试这个（未测试）。

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

上面的例子是只搜索一个单词。如果您想查找重复，请使用 Findnext

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

另一种.Find

将自动过滤器与上面的数组。请参阅这个链接。这将给你一个开始。

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

这篇关于VBA - 在列中找到多个字符串，然后删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！