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

  • Thread starter Thread starter Fredy
  • Start date Start date
F

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.
 
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)
 
Back
Top