此问题已在此处有答案：

Excel-vba compare, condition, delete（1个答案）
4天前关闭。
我正在尝试创建一个VBA代码，循环通过Sheet1中的E单元格。

• 如果E单元格为空，则删除整行。
• 如果E单元格的值存在于表2的A列中，则删除整行。
• 如果不满足上述条件，则不执行任何操作。

下面的代码删除了E调用为空的行，但没有删除表2的列A中存在E单元格值的行。

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
        
        'If value not found or cell is blank, delete entire row
        If Not found Or str = "" Then
            s1.Rows(i).Delete
        End If
    Next i
    
    'Display message when done
    MsgBox "Done deleting rows."
End Sub