View Full Version : Solved: Removing Duplicate Entries in a Column

07-12-2004, 06:34 AM
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)

Any suggestions before I go nuts?? :bawl :roll: :help

07-12-2004, 06:48 AM
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 SubHTH

Zack Barresse
07-12-2004, 07:02 AM
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?

07-12-2004, 07:03 AM
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!!

07-12-2004, 07:09 AM
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
07-12-2004, 09:33 AM
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!