looking for duplicates
 

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

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

looking for duplicates
 

this tuturial might help
http://www.mrexcel.com/articles/dupl...formatting.php

"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

looking for duplicates
 

If you have 2007 excel then under the data tab in the data tools section you
can apply a function called "Remove Duplicates"


"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

looking for duplicates
 

works great, thank you

" wrote:

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