Slow Code (short)

[royca]

Hi folks, I have a shared workbook that breaks if you sort and filter in certain ways because of blank cells. I would like it serialized when I use it, this code works but seems slower than I would expect. Is there a technique someone would teach me that might decrease the time this takes this to complete?

'col 3 has the unique identifier I would like to tie to an ordered serial number
Dim serial As Long
Dim row As Long

serial = 0
row = 3
While Sheets("data").Cells(row, 3) <> ""
If Sheets("data").Cells(row, 3) <> Sheets("data").Cells(row - 1, 3) Then
serial = serial + 1
End If
Sheets("data").Cells(row, 4) = serial
row = row + 1
Wend

 

[JLGWhiz]

This might be a little faster.

Sub t()
Dim c As Range, x As Long
With Sheets("data")
    For Each c In .Range("C3", .Cells(Rows.Count, 3).End(xlUp).Offset(-1))
        If c.Value <> c.Offset(1).Value Then
            c.Offset(1, 1) = x + 1
            x = x + 1
        End If
    Next
End With
End Sub

 

[Fluff]

try this

Sub AddSerial()

    Range("C2").Value = 1
    With Range("D3:D" & Range("C" & Rows.Count).End(xlUp).Row)
        .Formula = "=if(C3<>C2,D2+1,D2)"
        .Value = .Value
    End With
End Sub

Using Evaluate might be quicker still, but I can't get it to work