Macro to delete duplicate data in row
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
-----Original Message-----
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
.
|