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! |
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! |
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! |
All times are GMT +1. The time now is 02:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com