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.

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.