Write macro to delete duplicate records

[rayfarag]

How can you write a macro to delete duplicate records in an excel file with over 2000 records?

[Damon Ostrander]

Hi rayfarag,

Here is a simple macro that does this. This assumes that a "duplicate record" is a row whose cells ALL match the cells of a previous row. Just select all the rows you want duplicate records delete from and run the macro.

Sub DelDups()

' Deletes duplicate rows in the selected range.
' All columns in the selected range must be identical for
' a row to be deleted. The entire row, not just the selected
' cells in the row, will be deleted if a duplicate is found.
' The first instance of the duplicate row is the copy that
' will be retained.

Dim iRow As Long
Dim jRow As Long
Dim iCol As Integer
Dim LastRow As Long 'The last row in the selected range
Dim FirstRow As Long 'The first row in the selected range
Dim FirstCol As Integer
Dim LastCol As Integer
Dim DelCount As Long 'The count of duplicate rows removed
Dim DupFound As Boolean 'True if duplicate row found

DelCount = 0

FirstRow = Selection.Row
LastRow = FirstRow + Selection.Rows.Count - 1
FirstCol = Selection.Column
LastCol = FirstCol + Selection.Columns.Count - 1

For iRow = FirstRow To LastRow - 1

For jRow = iRow + 1 To LastRow

DupFound = True
For iCol = FirstCol To LastCol
DupFound = DupFound And (Cells(jRow, iCol) = Cells(iRow, iCol))
If Not DupFound Then Exit For
Next iCol
If DupFound Then
' Duplicate row found--delete it
Rows(jRow).Delete
LastRow = LastRow - 1
DelCount = DelCount + 1
End If

Next jRow

Next iRow

Beep
MsgBox DelCount & " duplicate rows deleted.", _
vbInformation, "Duplicate Removal Results"

End Sub

Keep Excelling.

Damon

PS. If this is not what you meant by duplicate record, let me know. It is even easier to delete records based on duplicate values in just one column.