Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicates | Excel Worksheet Functions | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
Duplicates in excel that aren't 100% DUPLICATES ... | Excel Discussion (Misc queries) | |||
Keep non-duplicates only | Excel Worksheet Functions | |||
Duplicates | Excel Worksheet Functions |