在Excel-VBA中删除所有重复的行

Question

我需要删除所有行而不留下任何唯一记录。如果存在重复，则删除所有匹配的行。Criteria是C列，如果C列中存在任何重复记录，则删除整行（包括unique）。

Sub DDup()

    Sheets("MobileRecords").Activate
    With ActiveSheet
        Set Rng = Range("A1", Range("C1").End(xlDown))
        Rng.RemoveDuplicates Columns:=Array(3, 3), Header:=xlYes
    End With

End Sub

Answer 1

我喜欢Jeeped的代码，但它不是最好的可读性。因此，这里有另一种解决方案。

Sub remDup()
Dim rng As Range, dupRng As Range, lastrow As Long, ws As Worksheet
Dim col As Long, offset As Long, found As Boolean

'Disable all the stuff that is slowing down
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Define your worksheet here
Set ws = Worksheets(1)

'Define your column and row offset here
col = 3
offset = 0

'Find first empty row
Set rng = ws.Cells(offset + 1, col)
lastrow = rng.EntireColumn.Find( _
                What:="", After:=ws.Cells(offset + 1, col)).Row - 1

'Loop through list
While (rng.Row < lastrow)
    Do
        Set dupRng = ws.Range(ws.Cells(rng.Row + 1, col), ws.Cells(lastrow, col)).Find( _
                What:=rng, LookAt:=xlWhole)
        If (Not (dupRng Is Nothing)) Then
            dupRng.EntireRow.Delete
            lastrow = lastrow - 1
            found = True
            If (lastrow = rng.Row) Then Exit Do
        Else
            Exit Do
        End If
    Loop

    Set rng = rng.offset(1, 0)

    'Delete current row
    If (found) Then
        rng.offset(-1, 0).EntireRow.Delete
        lastrow = lastrow - 1
    End If

    found = False
Wend

'Enable stuff again
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

它可以处理多个重复项，您可以定义行偏移量，该偏移量定义在列的开头忽略多少行。