Deleting duplicate rows.....there's more

[Fredy]

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.

 

[Bob Phillips]

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)