Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete duplicate data in row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete duplicate data in row
Chip Pearson has documented many methods for dealing with duplicates and
uniques See his page on this: http://www.cpearson.com/excel/duplicat.htm -- Regards, Tom Ogilvy "Tony" wrote in 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Duplicate Rows Macro Question | Excel Discussion (Misc queries) | |||
delete duplicate data | New Users to Excel | |||
Macro to delete duplicate data | Excel Programming | |||
Macro to delete duplicate/triplicate rows | Excel Programming | |||
Macro to delete duplicate/triplicate rows | Excel Programming |