eliminate duplicate rows

[Alphazulu]

I have a spreadsheet 10000 rows that have duplicate rows throughout the spreadsheet.

Is it possible to easily identify the duplicate rows (based on info in column C). Ultimately, I want to delete the duplicate rows.

Thanks

 

[just_jon]

If a row exists twice, do you want to

a] delete both instances, or

b] delete 1 instance, leaving one.

 

[Alphazulu]

I am looking to delete one version and leave one.


Thanks

 

[lenze]

A search of the board for "Deleting Duplicate Rows" shows this question has been asked in some form 95 times (probably more). Surely, one of those post would be helpful.

lenze

 

[just_jon]

Kristy's code [ with one modification ] from this thread is below, and should, I think, do what you want.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()
<SPAN style="color:#00007F">Dim</SPAN> NumRng <SPAN style="color:#00007F">As</SPAN> Range, ChkRng <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#007F00">'range of phone numbers (assuming D1 is a column header and data starts in D2)</SPAN>
<SPAN style="color:#007F00">' Note: Changed Kristy's code here to point to column=C and to be compatable w/ XL12</SPAN>
<SPAN style="color:#00007F">Set</SPAN> NumRng = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp))

<SPAN style="color:#007F00">'insert extra column that will be used to mark the duplicates</SPAN>
NumRng.Offset(, 1).EntireColumn.Insert
<SPAN style="color:#00007F">Set</SPAN> ChkRng = NumRng.Offset(, 1)

<SPAN style="color:#007F00">'filter column D to show only unique entries</SPAN>
NumRng.AdvancedFilter Action:=xlFilterInPlace, Unique:=<SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#007F00">'mark the visible rows with an 'x'</SPAN>
ChkRng.SpecialCells(xlCellTypeVisible).Value = "x"

<SPAN style="color:#007F00">'remove filter</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
ActiveSheet.ShowAllData

<SPAN style="color:#007F00">'delete any rows that do not have an 'x' in column E</SPAN>
ChkRng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

<SPAN style="color:#007F00">'delete the extra column</SPAN>
ChkRng.EntireColumn.Delete

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>