Macro to delete duplicate data in row

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

I need a macro that will delete duplicate data. I can
search on one column and delete the duplicate data. It is
possible that there is more than one duplication.
Ideally, I would like to keep the last entry, but I could
work around that if it was not possible. Any help would
be appreciated.

Tony
 
I have a non-macro solution and a macro solution. Both are
pretty quick and painless.

Non Macro
- Sort the table of data by the field that determines if
the record is a duplicate. (Column A in my example)
- In an empty column, type this formula into row 3
(assuming you have headings):
=If(A3=A2,"x","")
- Fill the formula down for as far as you have values in
that column.
- Filter on the x and delete those rows.
- What's left is only one instance of each.

Macro

Sub DeleteDups()
Dim LastRow As Integer
Dim i As Integer

LastRow = ActiveSheet.Range("A65536").End(xlUp).Row

ActiveSheet.Range("A1").CurrentRegion.Sort
Key1:=ActiveSheet.Range("A1"), Order1:=xlAscending,
Header:=xlYes
For i = LastRow To 3 Step -1
If ActiveSheet.Cells(i, 1).Value =
ActiveSheet.Cells(i - 1, 1).Value Then ActiveSheet.Rows
(i).Delete Shift:=xlUp
Next i

End Sub

tod
 
Back
Top