vba - 如果条目与另一个表重复，则从表中删除行

Question

自从我使用 VBA 以来已经有一段时间了，所以请耐心等待，因为我可能有点生疏了。

我有 2 张 table 。一个是我所说的包含几列的“已删除”列表，另一个是包含所有详细信息的更详细列表。我想要做的是在“已删除”列表中查找项目(参见表 1)，如果一行中的所有项目都与主列表中的相关项目匹配(参见表 2)，则删除该行。我不能只做第一列，因为数据的结构不是很好。

表1(已删除)

+-------------------+---------+-----------------+-----------------------+----------+
|       Name        | Source  |     Tel No      |       Address 1       | Postcode |
+-------------------+---------+-----------------+-----------------------+----------+
| A N OTHER         | MySrc   | 01234 123456    | 18 FAKE STREET        | XXX XXXX |
| A N OTHER         | MySrc2  | 01234 567890    | 29 FAKE STREET        | XXX XXXX |
+-------------------+---------+-----------------+-----------------------+----------+

表2(主要)

+---------------+-------+-----------+-----------+---------------+-------------+-------------------+----------------+----------------+----------------+----------+-------------+-------------+--------+--------+--------+--------+--------+--------+------------+--------+
| Name          | Title | Full Name | Job Title |  Tel No       | Tel No 2    |  Address Line 1   | Address Line 2 | Address Line 3 | Address Line 4 | Postcode |   Data 1    |   Data 2    | Data 3 | Data 4 | Data 5 | Data 6 | Data 7 | Data 8 | Date Added | Source |
+---------------+-------+-----------+-----------+---------------+-------------+-------------------+----------------+----------------+----------------+----------+-------------+-------------+--------+--------+--------+--------+--------+--------+------------+--------+
|  AN OTHER     |       | Person A  |           | 01234 123456  |             | 18 FAKE STREET    |                |                |                | XXX XXXX |             |             |        |        |        |        |        |        |            | MySrc  |
|  AN OTHER     |       | Person B  |           | 01234 999999  |             | 18 FAKE STREET    |                |                |                | XXX XXXX |             |             |        |        |        |        |        |        |            | MySrc  |
|... about another 5000 rows...
+---------------+-------+-----------+-----------+---------------+-------------+-------------------+----------------+----------------+----------------+----------+-------------+-------------+--------+--------+--------+--------+--------+--------+------------+--------+

从这里可以看出，它应该删除第 1 行但保留第 2 行。

我有以下我编写的 VBA 代码，它当前仅根据一列查找存在重复项的行。

Sub createFinalList()
Dim rng As Range, Dim r As Range
Dim wsFinal As Worksheet, wsOriginal As Worksheet, wsDelete As Worksheet

Set wsFinal = ThisWorkbook.Sheets("FinalList")
Set wsOriginal = ThisWorkbook.Sheets("List")
Set wsDelete = ThisWorkbook.Sheets("PermaDelete")

For i = wsDelete.UsedRange.Rows.Count To 2 Step -1
    Set r = wsOriginal.Columns(1).Find(wsDelete.Cells(i, 1).Value, , xlValues, xlWhole, xlByRows, xlNext)
    If Not r Is Nothing Then
        firstA = r.Address
        Set rng = Nothing
        Do
            If rng Is Nothing Then
                Set rng = wsOriginal.Rows(r.Row)
            Else
                Set rng = Union(r, rng)
            End If
            Set r = wsOriginal.Columns(1).Find(wsDelete.Cells(i, 1).Value, r, xlValues, xlWhole, xlByRows, xlNext)
            Debug.Print r.Address
        Loop Until firstA = r.Address
    End If
Next i
End Sub

然后我想做的是在删除最终结果之前在 .Find 上为每个后续列使用 rng，但是看起来应该有更简单的方法。我错过了一个技巧吗？有没有更简单的方法来做到这一点？

Answer 1

如果您的“已删除”数据与“主要”数据完全对应，您可以使用 高级过滤器 .在此处阅读有关高级过滤器的更多信息:http://www.excel-easy.com/examples/advanced-filter.html高温高压

Sub createFinalList()
    Dim mainSheet As Worksheet
    Dim criteriaSheet As Worksheet

    Set mainSheet = ThisWorkbook.Worksheets("Main")
    Set criteriaSheet = ThisWorkbook.Sheets("Deleted")

    Dim mainRange As Range
    Dim criteriaRng As Range

    Set mainRange = mainSheet.Range("A2:U3")
    Set criteriaRng = criteriaSheet.Range("A1:E3")

    mainRange.AdvancedFilter _
        Action:=xlFilterInPlace, _
        criteriaRange:=criteriaRng, _
        Unique:=False

    ' Delete rows hidden by advanced filter
    Dim myRow As Range
    Dim toDelete As Range

    For Each myRow In mainRange.Rows
        If myRow.EntireRow.Hidden Then
            If toDelete Is Nothing Then
                Set toDelete = myRow
            Else
                Set toDelete = Union(toDelete, myRow)
            End If
        End If
    Next

    If Not toDelete Is Nothing Then _
        toDelete.Delete
End Sub