Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicate rows.....there's more
Hello, does anyone know how to delete duplicate rows in
Excel.........there's more. The spreadsheet i have has about 20 columns and i need to delete rows where the data (text and number) is exactly the same in each column per row. Can anyone help? Thanks so much. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicate rows.....there's more
Freddy,
Here is one way Sub TidyUp() Dim cRows As Long cRows = Cells(Rows.Count, "A").End(xlUp).Row Range("A1").EntireColumn.Insert Range("A1").FormulaR1C1 = _ "=CONCATENATE(RC[1],RC[2],RC[3],RC[4],RC[5],RC[6])" Range("A1").AutoFill Destination:=Range("A1").Resize(cRows) Range("A1").EntireColumn.Insert With Range("A1") .EntireRow.Insert .FormulaR1C1 = "=COUNTIF(R2C2:R[0]C2,RC[1])" .AutoFill Destination:=Range("A2:A" & cRows + 1) End With Columns("A:A").AutoFilter Field:=1, Criteria1:="1", Operator:=xlAnd With Range("A1:A" & cRows + 1) .SpecialCells(xlCellTypeVisible).EntireRow.Delete End With Columns("A:B").EntireColumn.Delete End Sub Chnage this "=CONCATENATE(RC[1],RC[2],RC[3],RC[4],RC[5],RC[6])" to your target coilumns -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Fredy" wrote in message ... Hello, does anyone know how to delete duplicate rows in Excel.........there's more. The spreadsheet i have has about 20 columns and i need to delete rows where the data (text and number) is exactly the same in each column per row. Can anyone help? Thanks so much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting duplicate rows | Excel Discussion (Misc queries) | |||
deleting duplicate rows | Excel Discussion (Misc queries) | |||
Deleting duplicate rows | Excel Discussion (Misc queries) | |||
Deleting Duplicate Rows | Excel Discussion (Misc queries) | |||
Deleting Duplicate Rows | Excel Programming |