Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut/Paste duplicate rows?
I have a list of data in columns A:I. I need to find all duplicate rows in
the used range based on the data in col G, cut the duplicates and paste into another worksheet in the workbook. Can someone offer code to accomplish this? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut/Paste duplicate rows?
Sub ABC()
Dim rng As Range, rng1 As Range Set rng = Intersect(ActiveSheet.UsedRange.EntireRow, _ ActiveSheet.Columns(10)) rng.Formula = "=if(Countif($G$1:G1,G1)1,na(),"""")" On Error Resume Next Set rng1 = rng.SpecialCells(xlFormulas, xlErrors) On Error GoTo 0 If Not rng1 Is Nothing Then rng1.EntireRow.Copy Worksheets("Sheet2").Range("A1") rng1.EntireRow.Delete Worksheets("Sheet2").Columns(10).ClearContents End If ActiveSheet.Columns(10).ClearContents End Sub -- Regards, Tom Ogilvy "fpd833" wrote: I have a list of data in columns A:I. I need to find all duplicate rows in the used range based on the data in col G, cut the duplicates and paste into another worksheet in the workbook. Can someone offer code to accomplish this? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut/Paste duplicate rows?
Thanks Tom!! It worked like a charm with one caveat (and this is because I
wasn't clear in the original post): this routine leaves behind one of the duplicate rows. Lets say I have 3 rows that have the same data in col G, is it possible to cut and past all 3 rows into the other sheet? Thanks! "Tom Ogilvy" wrote: Sub ABC() Dim rng As Range, rng1 As Range Set rng = Intersect(ActiveSheet.UsedRange.EntireRow, _ ActiveSheet.Columns(10)) rng.Formula = "=if(Countif($G$1:G1,G1)1,na(),"""")" On Error Resume Next Set rng1 = rng.SpecialCells(xlFormulas, xlErrors) On Error GoTo 0 If Not rng1 Is Nothing Then rng1.EntireRow.Copy Worksheets("Sheet2").Range("A1") rng1.EntireRow.Delete Worksheets("Sheet2").Columns(10).ClearContents End If ActiveSheet.Columns(10).ClearContents End Sub -- Regards, Tom Ogilvy "fpd833" wrote: I have a list of data in columns A:I. I need to find all duplicate rows in the used range based on the data in col G, cut the duplicates and paste into another worksheet in the workbook. Can someone offer code to accomplish this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate rows into new rows based on row value | Excel Worksheet Functions | |||
Macro - Cut and paste a row if duplicate | Excel Programming | |||
Cut and paste duplicate data | Excel Programming | |||
Duplicate rows Elimination- change rows accordingly | Excel Programming | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) |