Excel两表比对查找不匹配单元格并复制到新表的VBA代码修正问题

原代码的逻辑是单元格级别的匹配，将工作表1的A、B列所有单元格值混合存储后逐个匹配工作表2的单个单元格，不符合你要求的行级优先匹配规则，同时存在边界参数缺失（未取行号）、复制范围不符合需求的问题。

Sub CompareSheets()
    Dim dicA As Object, dicB As Object, copyRng As Range
    Dim i As Long, lastRow1 As Long, lastRow2 As Long, targetRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    
    ' 绑定工作表对象，请替换为你实际的工作表名称或Codename
    Set ws1 = ThisWorkbook.Worksheets("工作表1")
    Set ws2 = ThisWorkbook.Worksheets("工作表2")
    Set ws3 = ThisWorkbook.Worksheets("工作表3")
    Set dicA = CreateObject("Scripting.Dictionary")
    Set dicB = CreateObject("Scripting.Dictionary")
    
    ' 读取工作表1的A列、B列值存入对应字典
    lastRow1 = ws1.Range("B" & ws1.Rows.Count).End(xlUp).Row
    For i = 2 To lastRow1
        If Not dicA.Exists(ws1.Cells(i, 1).Value) Then dicA(ws1.Cells(i, 1).Value) = True
        If Not dicB.Exists(ws1.Cells(i, 2).Value) Then dicB(ws1.Cells(i, 2).Value) = True
    Next i
    
    ' 遍历工作表2的每一行，按规则匹配
    lastRow2 = ws2.Range("B" & ws2.Rows.Count).End(xlUp).Row
    For i = 2 To lastRow2
        ' 优先匹配A列，匹配到直接跳过当前行
        If dicA.Exists(ws2.Cells(i, 1).Value) Then
            GoTo NextRow
        End If
        ' A列未匹配则匹配B列，匹配到直接跳过
        If dicB.Exists(ws2.Cells(i, 2).Value) Then
            GoTo NextRow
        End If
        ' 均未匹配，收集待复制的A、B列单元格
        If copyRng Is Nothing Then
            Set copyRng = ws2.Range(ws2.Cells(i, 1), ws2.Cells(i, 2))
        Else
            Set copyRng = Union(copyRng, ws2.Range(ws2.Cells(i, 1), ws2.Cells(i, 2)))
        End If
NextRow:
    Next i
    
    ' 批量复制到工作表3
    If Not copyRng Is Nothing Then
        targetRow = ws3.Range("A" & ws3.Rows.Count).End(xlUp).Row
        ' 处理工作表3为空的情况
        If targetRow = 1 And IsEmpty(ws3.Range("A1")) Then targetRow = 0
        copyRng.Copy ws3.Range("A" & targetRow + 1)
    End If
    
    ' 释放对象
    Set dicA = Nothing
    Set dicB = Nothing
    Set copyRng = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
End Sub

• 分别用两个字典存储工作表1A列、B列的所有去重值，避免逐行遍历工作表1，匹配效率更高
• 按行遍历工作表2，严格遵循优先级规则：A列匹配成功直接跳过当前行，不校验B列；A列匹配失败才校验B列
• 仅收集A、B列均匹配失败的行的A、B列内容，批量复制到工作表3末尾，符合需求
• 兼容工作表3无数据的初始场景，不会出现粘贴错位问题

内容的提问来源于stack exchange，提问作者TropicalMagic