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") Else Results = MsgBox("No duplicate rowsfound.", vbInformation, "Duplicate rows") End If End Sub