VBA Loops in Excel: For Next, For Each, Do While and Do Until

Part of the free Module 12: Excel VBA Course · Lesson 14 of 18 · Full Excel course

Loops in VBA repeat a block of code: a fixed number of times with For Next, once for every object in a collection with For Each, or until a condition changes with Do While and Do Until. In this lesson you will learn every VBA loop type in Excel with runnable examples, how Step and Exit For control a loop, and which loop to choose for rows, sheets and open-ended tasks.

The five loop types and when to use them

• For … Next: a counter runs from a start to an end value. Use it for rows, columns and any known count.
• For Each … Next: visits every member of a collection: worksheets, cells in a range, files in a folder.
• Do While … Loop: repeats while a condition is true; the test can be at the top or the bottom.
• Do Until … Loop: repeats until a condition becomes true.
• While … Wend: an older form of Do While kept for compatibility; prefer Do While in new code because it supports Exit Do.

For Next loop

Write the multiplication table of 12 in column A.

Sub For_Loop()
    Dim sh As Worksheet
    Dim i As Long
    Set sh = ThisWorkbook.Sheets("ForLoop")
    For i = 1 To 10
        sh.Range("A" & i).Value = 12 * i
    Next i
End Sub

The counter i takes the values 1 to 10 and the body runs once for each, so A1:A10 receives 12, 24 … 120.

Step in a For loop

Sub For_Loop_Step()
    Dim sh As Worksheet
    Dim i As Long
    Set sh = ThisWorkbook.Sheets("ForLoop")
    For i = 1 To 10 Step 2
        sh.Range("C" & i).Value = "PK-AnExcelExpert.com"
    Next i
    sh.Columns("C:C").AutoFit
End Sub

Step 2 increments the counter by two, so only odd rows are filled; a negative step such as For i = lastRow To 2 Step -1 runs backwards, which is essential when deleting rows.

Exit For

Stop the loop early once the answer is found. The list below holds country-wise sales in F:G; we want India’s figure only.

Sub Exit_For_Statement()
    Dim sh As Worksheet
    Dim i As Long
    Set sh = ThisWorkbook.Sheets("ForLoop")
    For i = 2 To sh.Cells(sh.Rows.Count, "F").End(xlUp).Row
        If sh.Range("F" & i).Value = "India" Then
            MsgBox "Sales of India is " & sh.Range("G" & i).Value
            Exit For
        End If
    Next i
End Sub

The upper limit is the last used row in column F, and Exit For leaves the loop as soon as India is found instead of scanning the remaining rows.

For Each loop

Sub For_Each()
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        MsgBox sh.Name
    Next sh
End Sub

No counter is needed; sh becomes each worksheet in turn. The same pattern works for every cell in a range: For Each c In sh.Range("A1:A10").

While Wend loop

Sub While_Wend_Loop()
    Dim sh As Worksheet
    Dim i As Long
    Set sh = ThisWorkbook.Sheets("While Wend Loop")
    i = 1
    While i < 11
        sh.Range("A" & i).Value = 20 * i
        i = i + 1        'increment, or the loop never ends
    Wend
End Sub

The condition is tested before each pass; the body must change i or the loop runs for ever.

Do While loop

Sub Do_While_Loop()
    Dim sh As Worksheet
    Dim i As Long
    Set sh = ThisWorkbook.Sheets("Do While Loop")
    i = 1
    Do While i < 11
        sh.Range("A" & i).Value = 10 * i
        i = i + 1
    Loop
End Sub

Do While repeats as long as the condition is true. Writing Do ... Loop While i < 11 instead tests at the bottom, guaranteeing at least one pass.

Do Until loop

Sub Do_Until_Loop()
    Dim sh As Worksheet
    Dim i As Long
    Set sh = ThisWorkbook.Sheets("Do Until Loop")
    i = 1
    Do Until i = 11
        sh.Range("A" & i).Value = 15 * i
        i = i + 1
    Loop
End Sub

Do Until is the mirror image: it repeats while the condition is false and stops when i reaches 11. A common use is Do Until IsEmpty(sh.Cells(r, 1)) to walk down a column until the first blank.

Complete example: delete blank rows and total every sheet

Sub Clean_And_Total_All_Sheets()
    Dim sh As Worksheet
    Dim lastRow As Long, r As Long
    Dim total As Double

    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> "Summary" Then
            lastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
            'delete blank rows from the bottom up
            For r = lastRow To 2 Step -1
                If Application.WorksheetFunction.CountA(sh.Rows(r)) = 0 Then sh.Rows(r).Delete
            Next r
            'total column E
            total = 0
            lastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
            For r = 2 To lastRow
                If IsNumeric(sh.Cells(r, "E").Value) Then total = total + sh.Cells(r, "E").Value
            Next r
            sh.Cells(lastRow + 1, "E").Value = total
            sh.Cells(lastRow + 1, "E").Font.Bold = True
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub

This runnable macro nests a For Each over the sheets with two For Next loops inside: one runs backwards to delete empty rows safely, the other sums column E and writes a bold total.

Tips and common mistakes

• Delete rows backwards. Deleting inside a forward loop skips the row that moves up; use Step -1.
• Infinite loops. If a Do loop never changes its condition, press Esc or Ctrl+Break to stop it. Save before testing.
• Use Long, not Integer, for counters that can exceed 32,767.
• Do not loop over whole columns. Limit to the last used row; For Each c In Columns("A") visits a million cells.
• Turn off ScreenUpdating and, when writing many cells, work on an array and write it back in one assignment.

Practice and real-world use

Write a loop that colours every row where column E is above average, another that unhides every sheet with For Each, and a Do Until loop that reads down a column until the first blank cell. Batch report generation, data validation sweeps and multi-sheet consolidation are all loops at their core.

Click here to download the practice file.

Related lessons

• If Then Else and ElseIf
• Cells and Range objects
• VBA and worksheet functions
• Excel VBA course hub

Frequently asked questions

What is the difference between For Next and For Each in VBA?

For Next runs a numeric counter between two values and is ideal for rows and columns. For Each visits every object in a collection, such as all worksheets or all cells in a range, without needing a counter.

How do I exit a loop early in VBA?

Use Exit For inside a For or For Each loop and Exit Do inside a Do While or Do Until loop. While Wend has no exit statement, which is one reason to prefer Do While.

Why does my loop skip rows when deleting?

Deleting a row shifts the rows below it up, so the next iteration jumps over one. Loop from the last row to the first with Step -1 and every row is examined.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.