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.
|