VBA Loops Explained: For, For Each, Do While and Do Until

📎 This article includes 1 downloadable practice file ↓

Loops are where macros start saving real time: doing the same thing to every row, every sheet or every file. VBA has four loop styles. Knowing which one to pick — and how to make loops fast — covers most of what you need.

For…Next: a known number of times

Dim r As Long
For r = 2 To 101
    ws.Cells(r, "C").Value = ws.Cells(r, "A").Value * ws.Cells(r, "B").Value
Next r

Use it whenever you know where to start and stop, usually from row 2 to the last row.

Step: skip or go backwards

For r = 2 To lastRow Step 2       ' every second row
For r = lastRow To 2 Step -1      ' bottom to top — needed when deleting rows

For Each: every item in a collection

Dim c As Range
For Each c In ws.Range("B2:B500")
    If IsNumeric(c.Value) And c.Value < 0 Then c.Font.Color = vbRed
Next c

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
    sh.Range("A1").Font.Bold = True
Next sh

For Each reads naturally and works on ranges, sheets, workbooks, shapes, chart series and more. You don’t need a counter.

Do While and Do Until: until a condition changes

r = 2
Do While ws.Cells(r, "A").Value <> ""        ' stop at the first blank
    ws.Cells(r, "D").Value = UCase$(ws.Cells(r, "A").Value)
    r = r + 1
Loop

Do Until ws.Cells(r, "A").Value = "TOTAL"     ' stop at a marker row
    r = r + 1
Loop

Leaving a loop early

For r = 2 To lastRow
    If ws.Cells(r, "A").Value = "C-104" Then
        foundRow = r
        Exit For
    End If
Next r

Exit For and Exit Do stop as soon as you have what you need — no point scanning 50,000 rows after the match.

Nested loops

For r = 2 To 13                ' months down
    For c = 2 To 6             ' regions across
        ws.Cells(r, c).Value = Application.WorksheetFunction.SumIfs( _
            data.Columns(5), data.Columns(1), ws.Cells(r, 1).Value, data.Columns(3), ws.Cells(1, c).Value)
    Next c
Next r

Inner loop runs completely for each step of the outer one: 12 × 5 = 60 calculations here.

Make loops fast

Sub FastLoop()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    On Error GoTo Done
    ' ... your loop ...
Done:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

These three settings often make loops 5–20 times faster. For really big data, read the range into an array and loop in memory (lesson 9) — that’s another 100× on top.

Which loop?

Where people go wrong

• Endless Do loops — counter never changes or condition never becomes false.
• Using Integer counters — overflow past row 32,767. Use Long.
• Changing the collection you loop through (deleting sheets inside For Each sh) — loop backwards by index instead.

Practice

The workbook has a sheet for each loop type with a task and a solution button, plus a slow loop to speed up with the three settings above — the timer shows the difference.

Next: error handling and debugging.