Excel / VBA 通过交叉引用 2 个不同的工作表然后删除 1 行来删除重复行

Question

我有 2 张单独的工作表，我们称它们为工作表 A、工作表 B。我在工作表 B 中有数据，该数据也在工作表 A 中。我想找到那些相等的行并将它们从工作表 B 中删除。

我无法合并这 2 张工作表并使用过滤器，因为我正在执行动态 SQL 来查询不同的数据。

每张工作表都有一个唯一的键列，

我可以接受 VBA 建议和 Excel 公式。只要我不合并床单即可。

非常感谢大家！

抱歉，显然我犯了一个错误。这里某处存在无限循环。顺便说一句，这是本的回答。我刚刚重新发布了一个可编译的版本。

    Sub CleanDupes()
    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim keyColA As String
    Dim keyColB As String
    Dim rngA As Range
    Dim rngB As Range
    Dim intRowCounterA As Integer
    Dim intRowCounterB As Integer


    keyColA = "A"
    keyColB = "A"

    intRowCounterA = 1
    intRowCounterB = 1

    Set wsA = Worksheets("Sheet2")
    Set wsB = Worksheets("Sheet1")

    Do While Not IsEmpty(wsA.Range(keyColA & intRowCounterA).Value)


        Set rngA = wsA.Range(keyColA & intRowCounterA)

         intRowCounterB = 1
        Do While Not IsEmpty(wsB.Range(keyColB & intRowCounterB).Value)

            Set rngB = wsB.Range(keyColB & intRowCounterB)

            If rngA.Value = rngB.Value Then

                 Rows(intRowCounterB).EntireRow.Delete
                 intRowCounterB = intRowCounterB - 1


            End If
              intRowCounterB = intRowCounterB + 1
        Loop
        intRowCounterA = intRowCounterA + 1
    Loop
End Sub

Answer 1

Sub CleanDupes()
    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim keyColA As String
    Dim keyColB As String
    Dim rngA As Range
    Dim rngB As Range
    Dim intRowCounterA As Integer
    Dim intRowCounterB As Integer
    Dim strValueA As String


    keyColA = "A"
    keyColB = "B"

    intRowCounterA = 1
    intRowCounterB = 1

    Set wsA = Worksheets("Sheet A")
    Set wsB = Worksheets("Sheet B")

    Do While Not IsEmpty(wsA.Range(keyColA & intRowCounterA).Value)
        intRowCounterB = 1
        Set rngA = wsA.Range(keyColA & intRowCounterA)
        strValueA = rngA.Value
        Do While Not IsEmpty(wsB.Range(keyColB & intRowCounterB).Value
            Set rngB = wsB.Range(keyColB & intRowCounterB)
            If strValueA = rngB.Value Then
                 'Code to delete row goes here, but I'm not sure exactly'
                 'what it is.'
                 wsB.Rows(intRowCounterB).Delete
                 intRowCounterB = intRowCounterB - 1
            End If
            intRowCounterB = intRowCounterB + 1
        Loop
        intRowCounterA = intRowCounterA + 1
    Loop
End Sub

这应该可以帮助你开始。