Do while loop very slow?

Dear all,

In my excel file I have a dataset for which I need to re-label some items. Below I have a part of the code that looks at a certain value and then decides the final label. The whole dataset is approx 30000 lines long but it takes forever to complete this (simple?) task?

I would be keen to understand why this is so slow as I need this type of loop coding for other queries as well.

Thanks!

Bundi

Sub Update_data()

' 0. Variables
' ============

' Variables for loops
Dim i As Long
Dim viapoint As String

' 1. Computation
' ==============

' Desactivate display of Sheets during computation
Application.ScreenUpdating = False
    
    'First dataline
    i = 2
    
    'Start Loop
    Do While Not IsEmpty(Cells(i, 1))
    
    'In loop variables
    viapoint = Cells(i, 4)
    
        ' Step 1 Flow
        
        Select Case viapoint
        
        Case "Direct traffic"
        
        Cells(i, 10) = "Direct"
        
        Case Else
        
        Cells(i, 10) = "Indirect"
        
        End Select
        
        i = i + 1
        
    Loop
    
    Application.ScreenUpdating = True
    End Sub

As mehmetick said it's best to avoid loops where possible. Try

Sub ReplaceLoop()
   With Range("J2:J" & Range("A" & Rows.Count).End(xlUp).row)
      .Value = Evaluate(Replace("If(@=""Direct traffic"",""Direct"",""Indirect"")", "@", .Offset(, -6).Address))
   End With
End Sub

Loops can also be quite fast ...

2.5s to 3.0s for 60.000 rows, for a simple data system (see attachment):

Sub Update_data_1()
Dim i As Long: i = 2
Dim valTime As Single: valTime = Timer

    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Do Until Cells(i, 1).Value = ""
        If Cells(i, 4).Value = "Direct traffic" Then Cells(i, 10).Value = "Direct" Else Cells(i, 10).Value = "Indirect"
        i = i + 1
    Loop
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    MsgBox CStr(Timer - valTime)
End Sub

0.25s to 0.30s for 60.000 rows, for a simple data system (see attachment):

Sub Update_data_2()
Dim i As Long: i = Cells(Rows.Count, 1).End(xlUp).Row
Dim uprbnd As Long: uprbnd = i - 1
Dim valTime As Single: valTime = Timer
Dim tbl()

    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    tbl = Range("d2:d" & i).Value
    For i = 1 To uprbnd
        If tbl(i, 1) = "Direct traffic" Then tbl(i, 1) = "Direct" Else tbl(i, 1) = "Indirect"
    Next
    Range("j2:j" & i).Value = tbl
    Erase tbl
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    MsgBox CStr(Timer - valTime)
End Sub

Not to nitpick on the above answers (though the solutions are indeed correct), but saying that loops are slow, or best avoided is touted frequently (though usually correctly) without context.

There's nothing wrong or slow with loops per se, what are very slow however, are worksheet calls from VBA. Interactions with the worksheet are best done as infrequently as possible and in bulk, the reason that your code is slow is that you are looping through a worksheet range, and within each iteration of the loop performing several worksheet calls.

In summary, the formula solutions above are fast, not specifically because they avoid looping, rather they avoid looping through the worksheet. Instead, they leverage Excel's native Formula engine that is much faster than accessing the worksheet object model through VBA.