Removing Duplicate Entries in a Column

[BexleyManor]

I have a worksheet with some 13,000 entries in column C. I would like my code to look down the column and delete the line above if the text value is the same. I wrote the following but found Excel 97 would simply crash.

On Error Resume Next
Dim cell As Range
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Sheet1")
Set cell = wks.Range("c2") ' start at the second item
Do While cell.Value <> ""
    If cell.Value <> cell.Offset(-1, 0).Value Then
        cell.EntireRow.Delete ' Delete row
    End If
    Set cell = cell.Offset(1, 0)
Loop

Any suggestions before I go nuts??

[Richie(UK)]

Hi BM,

Welcome to the board.

OK, a few points:

* How about using the 'unique' option in the Advanced Filter? Would that work for you?

* Beware of the blanket use of 'On Error Resume Next' - those error messages could point you in the right direction for spotting the problem with your code.

* When deleting rows its easy to lose track of the row that you are dealing with within a loop. For this reason its often easiest to establish the end of the range first and then loop backwards using Step-1. The idea is illustrated in this simple example:

Sub Test()
    Dim i As Long
Application.ScreenUpdating = False
    With Sheet1
    For i = .Cells(Rows.Count, "C").End(xlUp).Row To 2 Step -1
        If .Cells(i, "C").Value = "Mangoes" Then .Cells(i, "C").EntireRow.Delete
    Next i
    End With
    Application.ScreenUpdating = True
End Sub

HTH

[Zack Barresse]

Maybe this..

Sub testDup()
    Dim r As Long, x As Long
    r = Range("C65536").End(xlUp).Row
    For x = 2 To r Step 1
    If Range("C" & r).Value = Range("C" & r).Offset(-1).Value Then
        Range("C" & r).Offset(-1).EntireRow.Delete
    End If
    r = r - 1
    Next x
End Sub

Richie: The only reason I didn't step through backwards was because of the specifics on looking to delete the row above it, where that is somewhat 'traditionally' backwards. What do you think?

[BexleyManor]

Hi Richie,

Thanks for the advice.

Thought about going down the 'advanced filter' option but prefered a coded solution.

in your line of code If .Cells(i, "C").Value = "Mangoes", rather than looking specific txt like "Mangoes" how would I go about looking at the cell above, would I use offset, if so, how??

Thanks in advance!!

[BexleyManor]

Zack/Richie, many thanks my friends. Your code worked a treat. I just adapted it to my worksheet and everything went fine, no crashes or hangs.

Super work folks, much appreciated.

[Anne Troy]

Hi, Bexley! Welcome to VBAX!

I am in awe when I see what these guys can do... I don't think it'll ever cease to amaze me.

I just jumped in to bring your attention to VBA tags available in our forum here. Just check out the link in my signature when you get a chance to learn how to use them. You'll see that I edited your original post to display your VBA so it LOOKS like VBA now.

Again, welcome!