looking for duplicates

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a spreadsheet that has several thousand rows. I want to be able to
run a macro, or something, that will go through column A and return ONLY the
rows that have duplicate values in column A and delete all the other rows.

How would I do this???

Thanks in advance
 
If you have 2007 excel then under the data tab in the data tools section you
can apply a function called "Remove Duplicates"
 
works great, thank you

[email protected] said:
Hi,

This isn't entirely clear but I think you want to delete records that
aren't duplicated. so if a record exists only once this will delete
it. If so try this, if not post back and clarify.

Sub Please_delete_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If WorksheetFunction.CountIf(Range("A:A"), c.Value) = 1 Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike
Click to expand...
 
Back
Top