Removing Duplicate Entries in a Column

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.

Code:

---

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?? :bawl :roll: :help

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:

Code:

---

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

Maybe this..

Code:

---

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?

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!!

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.

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!