Deleting Duplicate Rows

[longbow007]

Hello, could someone please help me with the following:

In my Excel 2003 worksheet I need a macro to search column B for duplicate entries.

The data in column B is both numeric and string.

If there are any duplicate entries, then I need the duplicate rows to be deleted.

If any one could please help - it would be greatly appreciated.

Kind regards,

Chris

[Leith Ross]

Hello Chris,

Post a few examples of the column "B" strings. Alphanumeric strings should not present a problem when identifying duplicates.

[JBeaucaire]

Here are two of my existing macros for this task, perhaps one will suit you when adjusted. The first one should be faster on large datasets:

Sub DeleteDupesOnly()
'Deletes duplicates leaving complete list of unique values only
'Column B is evaluated
Dim LR As Long

'Data needs to start at row 2, so we'll insert a row
    Rows(1).Insert (xlShiftDown)
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Range("AA2:AA" & LR).FormulaR1C1 = "=COUNTIF(R2C2:RC2,RC2)"
    Range("AA1") = "Key"
    Range("AA1").AutoFilter Field:=1, Criteria1:=">1"
    Range("AA2:AA" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Rows(1).Delete (xlShiftUp)
    Columns("AA:AA").Clear
End Sub


Sub DeleteDupesByLoop()
Dim LR As Long, MyCount As Long
LR = Range("B" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
Application.EnableEvents = False

For i = LR To 2 Step -1
    MyCount = WorksheetFunction.CountIf(Cells(i, "B"), Columns("B:B"))
    If MyCount > 1 Then Rows(i).EntireRow.Delete Shift:=xlUp
    MyCount = 0
Next i

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[Richard Buttrey]

Hi Chris,

Use a helper column, say C. Assuming your data is in B1:B100 enter in C1

=COUNTIF($B1:$B100,B1)

and copy this down column C.

Now filter on column C for values > 1 which represent those rows which have duplicate values lower down the list.

HTH

[longbow007]

thanks JBeaucaire, Leith Ross and Richard Buttrey for all your excellent help and assistance - it is very much appreciated.

Kind regards,

Chris