Archived Software Uncategorized

Microsoft Excel – Delete duplicate rows

I’ve been meaning to put this online for some time but kept forgetting to get it done.  This article is simple and provides a small Excel VBA script that makes it easy to delete duplicate rows from an Excel worksheet.

All you need to do is copy the code into an Excel module or class, depending on how your Excel application works.  Then you just provide the relevant row ranges and the script will do the rest.

Sub DeleteDuplicateRows()
    ' Chris Rasmussen January 2011
    ' DeleteDuplicates
    '   goes through the range specified below and, if a duplicate value is found anywhere in the range, deletes that value's entire row
    Dim x As Long
    Dim LastRow As Long
    Dim duplicateCount As Integer

    duplicateCount = 0

    LastRow = Range("A300").End(xlUp).Row
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
            Range("A" & x).EntireRow.Delete
            duplicateCount = duplicateCount + 1
        End If
    Next x
    If duplicateCount > 0 Then
        Results = MsgBox(duplicateCount & " duplicate rows removed.", vbInformation, "Duplicate rows")
        Results = MsgBox("No duplicate rowsfound.", vbInformation, "Duplicate rows")
    End If
End Sub

Easy.  🙂