removing duplicates

[alliejane]

I am trying to delete a row if the value in one cell is a duplicate of the one above it. I can't compare the entire row because the values in the other cells may or may not be the same as the row above.

what I have right now works, but boy is is SLOW - and all sorts of blinks going on! Wondered if anyone has a suggestion for making it more efficient:

Dim TotalRows As Integer
Dim Row As Integer

TotalRows = ActiveSheet.UsedRange.Rows.Count
For Row = TotalRows To 2 Step -1
If Cells(Row, 3).Value = Cells(Row - 1, 3).Value Then
Rows(Row).Delete
End If
Next Row
End Sub

[tpoynton]

[vba]
application.screenupdating = false

Dim TotalRows As Integer
Dim Row As Integer

TotalRows = ActiveSheet.UsedRange.Rows.Count
For Row = TotalRows To 2 Step -1
If Cells(Row, 3).Value = Cells(Row - 1, 3).Value Then
Rows(Row).Delete
End If
Next Row

application.screenupdating = true

[/vba] that should get rid of the blinks and make things a little quicker

EDIT - also, check out the article 'optimize your code' available here

[alliejane]

a little quicker? Tons! Thanks so much.

Could you enlighten me as to why those statements helped so much, please?

[lucas]

Try this one for column C:
[VBA]Sub RemoveDuplicates()
Worksheets("Sheet1").Range("c1").Sort _
key1:=Worksheets("Sheet1").Range("c1")
Set currentCell = Worksheets("Sheet1").Range("c1")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Loop
End Sub[/VBA]

[tpoynton]

Could you enlighten me as to why those statements helped so much, please?

not to be evasive, but it's in the article, and johnske explains it much better than me simply saying 'it turns off screen updating'

you should really read that article 'cover to cover'; i didnt read it until after I developed a few things and wish I had learnt it sooner.