求助：修复VBA代码，删除Sheet1中E列空值或匹配Sheet2A列的行

核心错误原因 #

原代码的删除条件逻辑完全写反了：

• 你需要删除的是「E列单元格为空」或「E列值存在于Sheet2的A列」的行，对应逻辑应该是found = True（值存在）或者str = ""（单元格为空）时执行删除。
• 但原代码写的是If Not found Or str = "" Then，也就是值不存在于Sheet2A列或者为空才删除，这直接导致符合「值存在于Sheet2A列」条件的行根本不会被处理。

修正后的代码 #

基础修正版（保留原循环逻辑） #

仅修改判断条件即可解决问题：

Sub DeleteRows()
    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim lastRowS1 As Long
    Dim i As Long
    Dim j As Long
    Dim str As String
    Dim found As Boolean
    
    'Set worksheet objects
    Set s1 = ThisWorkbook.Sheets("Sheet1")
    Set s2 = ThisWorkbook.Sheets("Sheet2")
    
    'Get last row of Sheet1
    lastRowS1 = s1.Cells(s1.Rows.Count, "E").End(xlUp).Row
    
    'Loop through rows in Sheet1 (从下往上遍历避免跳行)
    For i = lastRowS1 To 1 Step -1
        str = s1.Cells(i, "E").Value
        
        'Check if value exists in Sheet2 column A
        found = False
        For j = 1 To s2.Cells(s2.Rows.Count, "A").End(xlUp).Row
            If str = s2.Cells(j, "A").Value Then
                found = True
                Exit For
            End If
        Next j
        
        '修正条件：为空 或 值存在于Sheet2A列时删除
        If found Or str = "" Then
            s1.Rows(i).Delete
        End If
    Next i
    
    MsgBox "Done deleting rows."
End Sub

高效优化版（用Find替代循环，提升大数据量下的效率） #

如果Sheet2的A列数据量较大，循环遍历会拖慢执行速度，改用Range.Find方法效率更高：

Sub DeleteRows_Efficient()
    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim lastRowS1 As Long
    Dim i As Long
    Dim targetValue As Variant
    Dim findResult As Range
    
    Set s1 = ThisWorkbook.Sheets("Sheet1")
    Set s2 = ThisWorkbook.Sheets("Sheet2")
    
    lastRowS1 = s1.Cells(s1.Rows.Count, "E").End(xlUp).Row
    
    '从下往上遍历，防止删除行后索引错乱
    For i = lastRowS1 To 1 Step -1
        targetValue = s1.Cells(i, "E").Value
        
        '判断空值
        If IsEmpty(targetValue) Or targetValue = "" Then
            s1.Rows(i).Delete
            GoTo NextRow
        End If
        
        '用Find查找Sheet2A列是否存在目标值
        Set findResult = s2.Range("A:A").Find( _
            What:=targetValue, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            MatchCase:=False)
        
        '如果找到则删除行
        If Not findResult Is Nothing Then
            s1.Rows(i).Delete
        End If
        
NextRow:
    Next i
    
    MsgBox "Done deleting rows."
End Sub

额外注意事项 #

• 遍历删除行必须从最后一行往上遍历（Step -1），否则删除行后后续行的索引会错乱，导致漏删。原代码这部分逻辑是正确的。
• 如果需要区分大小写匹配，把优化版代码中的MatchCase:=False改成True即可。
• 若Sheet2的A列有重复值，Find方法只要找到第一个匹配项就会返回，不影响删除逻辑。

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