looking for duplicates
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
On Fri, 25 Sep 2009 11:20:01 -0700, John
wrote:
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
|