删除重复项，其中同一行中另一列的值= 0

这是VBA中的一种简单方法，可以像表格一样查询电子表格。您必须针对特定情况调整RunQuery（）方法，因为我不知道您的列名。

这假定以下条件：

1. 您的工作表的布局像表格一样，列名位于第1行，数据位于其下方
2. 运行此代码之前，您已经保存了工作簿。

在我的特定工作簿中，我有一个标记为“类别”的列，一个标记为“类型”的列，并且添加了一个称为“ DeleteMe”的列。

'Adapt RunQuery for your particular needs
Sub RunQuery()
    'Change this SQL statement to reflect your particular need
    SQL = "SELECT [Category], [Types] FROM %table(0)% GROUP BY [Category], [Types] HAVING Count(*) > 1"

    'Change "Sheet2" to your sheet name
    Set rs = QuerySheet(SQL, Sheet2)

    Do While Not rs.EOF
        SQL = "UPDATE %table(0)% SET [DeleteMe]='x' WHERE [Category]='" & rs!Category & "' AND [Types]='" & rs!Types & "'"
        'Again, change Sheet2 to your sheet name
        QuerySheet SQL, Sheet2
        rs.MoveNext
    Loop
End Sub

'You don't need to change this function
Function QuerySheet(sSQL, ParamArray Worksheets())
    Dim oSh
    Dim sConn, n

    On Error GoTo QuerySheet_Err
    Set QuerySheet = CreateObject("ADODB.Recordset")
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Worksheets(0).Parent.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"

    n = 0
    For Each oSh In Worksheets
        sSQL = Replace(sSQL, "%table(" & n & ")%", "[" & oSh.Name & "$]")
        n = n + 1
    Next
    QuerySheet.Open sSQL, sConn
    Exit Function

QuerySheet_Err:
    MsgBox "Error in QuerySheet(): " & Err.Description
    End
End Function

资源

该代码用x更新“ DeleteMe”列。现在，您可以按“ DeleteMe”进行排序，并删除行（或编写一个简单的VBA语句来做到这一点）。

您的RunQuery（）方法将看起来更像这样，用工作表标题行中字段的名称替换“ B列”和“ M列”：

    ...

    'Change this SQL statement to reflect your particular need
    SQL = "SELECT [column B] FROM %table(0)% WHERE [column M] = 0 GROUP BY [column B] HAVING Count(*) > 1"

    'Change "Sheet2" to your sheet name
    Set rs = QuerySheet(SQL, Sheet2)

    Do While Not rs.EOF
        SQL = "UPDATE %table(0)% SET [DeleteMe]='x' " & _
           " WHERE " & _
           IIf(IsNull(rs![column B]), _
               " [column B] IS NULL", _
               " [column B]='" & rs![column B] & "' ") & _
           " AND [column M]=0"

        'Again, change Sheet2 to your sheet name
        QuerySheet SQL, Sheet2

    ...

注意：有时，当您将工作表的名称从“ Sheet1”更改为其他名称时，您会收到调试错误...尚未解决该问题。但这对于“ Quick N'Dirty”查询非常有效。