VBA比对多工作表列值删除匹配行 代码运行无效果求助

原代码核心错误 #

• 工作表引用不明确：匹配判断、执行删除的Range("E" & i)没有指定所属工作表，默认指向运行代码时的活动工作表，若活动工作表不是Sheet1，就会出现匹配不到、无执行效果的问题。
• 遍历范围不合理：Sheet1第一行是表头，原代码从第1行开始遍历，可能误删表头，也会浪费匹配资源，应该从数据起始行（第2行）开始遍历。
• 命名冲突：过程名Delete是VBA内置关键字，用作自定义过程名可能引发未知异常。
• 行号更新不及时：每轮删除行后Sheet1的总数据行数会减少，原代码没有同步更新LastRow，可能出现匹配遗漏。

修正后可运行代码 #

Sub 删除匹配ITEMID行()
    Dim LastRow As Long
    Dim i As Long
    Dim rngCell As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    ' 绑定工作表，避免活动表变动导致的错误
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    ' 动态获取Sheet1 E列最后一行有数据的行号
    LastRow = ws1.Cells(ws1.Rows.Count, "E").End(xlUp).Row
    
    For Each rngCell In ws2.Range("A1:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row)
        ' 从最后一行数据倒序遍历到第2行（跳过表头）
        For i = LastRow To 2 Step -1
            If ws1.Range("E" & i).Value = rngCell.Value Then
                ws1.Range("E" & i).EntireRow.Delete
            End If
        Next i
        ' 每轮删除后更新最后行号，避免行号错位
        LastRow = ws1.Cells(ws1.Rows.Count, "E").End(xlUp).Row
    Next rngCell
    ' 释放对象资源
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

高效优化方案（可选） #

当数据量较大时，使用字典存储待删除的ITEMID，单次遍历Sheet1即可完成匹配删除，执行效率远高于双重循环：

Sub 高效删除匹配行()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim delDict As Object
    Dim LastRow As Long, i As Long
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set delDict = CreateObject("Scripting.Dictionary")
    
    ' 把Sheet2待匹配的ITEMID存入字典
    LastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    For i = 1 To LastRow
        If Not delDict.exists(ws2.Range("A" & i).Value) Then
            delDict.Add ws2.Range("A" & i).Value, ""
        End If
    Next i
    
    ' 单次遍历Sheet1完成删除
    LastRow = ws1.Cells(ws1.Rows.Count, "E").End(xlUp).Row
    For i = LastRow To 2 Step -1
        If delDict.exists(ws1.Range("E" & i).Value) Then
            ws1.Range("E" & i).EntireRow.Delete
        End If
    Next i
    
    Set delDict = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

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