excel - Excel - 如果特定列中的单元格包含在另一列中找到的值，则删除行

Question

如果在另一列中找到供应商列中的值，我想删除所有行。（lookupVendor Col 在另一张表中）

MainSheet

LookupColumn

VendorCol
google 
microsoft
apple
vendorA
VendorB

这是我所拥有的，但它只删除一个值。如何让它使用整个 LookupColumn 并检测在 lookupColumn 中找到 colc 值的任何行。我的简单 VBA 代码一次处理一个值。（效率不高）

Sub Find_Vendor()
    Dim rng As Range
    Dim what As String
    what = "Microsoft"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            Rows(rng.Row).Delete
        End If
    Loop
End Sub

Mainsheet：包含 600,000 行 LookupColumn 包含 400 个条目。对于我拥有的大量数据，现有代码非常慢。

标签： excelvbaexcel-formula

Answer 1

使用自动过滤器（）：

Option Explicit

Public Sub Find_Vendor()
    Dim filters As Variant
    With Sheets("LookupColumnSheet") ' change "LookupColumnSheet" to your actual sheet with lookup values name
        filters = Application.Transpose(.Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).Value) ' collect "LookupColumnSheet" sheet column A values from row 1 down to last not empty row
    End With

    With Sheets("MainSheet") 'reference "MainSheet" sheet (change "MainSheet" to your actual "MainSheet" sheet name
        With .Range("C1", .Cells(.Rows.Count, 3).End(xlUp)) ' reference referenced sheet column C cells from row 1 down to last not empty one
            .AutoFilter Field:=1, Criteria1:=filters, Operator:=xlFilterValues ' filter referenced range with values from "LookupColumnSheet" sheet column A
            If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete ' if any filtered cells other than header then delete their entire row
        End With
        .AutoFilterMode = False ' remove filters
    End With
End Sub