More from this guide:

Edit Code Loops If Then Else Ranges Error Handling Custom Functions Arrays Worksheets File Operations Debugging Best Practices

VBA Loops: For, Do While, Do Until

Master all VBA loop types to automate repetitive tasks efficiently

Quick Reference

For Next: Loop a specific number of times

For Each: Loop through collection items

Do While: Loop while condition is true

Do Until: Loop until condition becomes true

Understanding VBA Loops

Loops allow you to repeat code multiple times without writing it repeatedly. VBA offers several loop types for different situations.

For Loops

✓ Known number of iterations
✓ Counter-based
✓ Best for: Arrays, ranges, sequences

Do Loops

✓ Unknown number of iterations
✓ Condition-based
✓ Best for: Until criteria met

For...Next Loop

Use when you know exactly how many times to repeat code.

Basic Syntax:

For counter = start To end [Step increment]

' Code to repeat

Next counter

Example 1: Loop 1 to 10

Sub CountToTen()

Dim i As Integer

For i = 1 To 10

Debug.Print i ' Prints: 1, 2, 3... 10

Next i

End Sub

Example 2: Loop Through Rows

Sub LoopRows()

Dim i As Long

For i = 1 To 100

Cells(i, 1).Value = "Row " & i

Next i

End Sub

Fills A1:A100 with "Row 1", "Row 2", etc.

Example 3: Using Step (Count by 2s)

Sub CountByTwos()

Dim i As Integer

For i = 0 To 10 Step 2

Debug.Print i ' Prints: 0, 2, 4, 6, 8, 10

Next i

End Sub

Example 4: Countdown (Negative Step)

Sub Countdown()

Dim i As Integer

For i = 10 To 1 Step -1

Debug.Print i ' Prints: 10, 9, 8... 1

Next i

MsgBox "Blastoff!"

End Sub

Pro Tip: Use Long instead of Integer for large row counts (>32,767).

For Each...Next Loop

Iterate through all items in a collection without counting.

Basic Syntax:

For Each item In collection

' Code to process item

Next item

Example 1: Loop Through Range

Sub LoopCells()

Dim cell As Range

For Each cell In Range("A1:A10")

cell.Value = cell.Value * 2 ' Double each value

Next cell

End Sub

Example 2: Loop Through Worksheets

Sub ListSheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

Debug.Print ws.Name

Next ws

End Sub

Example 3: Loop Through Array

Sub LoopArray()

Dim colors() As Variant

Dim color As Variant

colors = Array("Red", "Green", "Blue")

For Each color In colors

Debug.Print color

Next color

End Sub

When to use: For Each is cleaner when you need to process every item and don't need the index number.

Do While Loop

Repeats code while a condition is true. Checks condition before each iteration.

Basic Syntax:

Do While condition

' Code to repeat

Loop

Example 1: Find Last Row

Sub FindLastRow()

Dim row As Long

row = 1

Do While Cells(row, 1).Value <> ""

row = row + 1

Loop

MsgBox "Last row with data: " & (row - 1)

End Sub

Example 2: Loop Until Value Found

Sub FindValue()

Dim row As Long

row = 1

Do While Cells(row, 1).Value <> "Target"

row = row + 1

If row > 1000 Then Exit Do ' Safety exit

Loop

MsgBox "Found at row: " & row

End Sub

Example 3: User Input Loop

Sub GetInput()

Dim userInput As String

userInput = ""

Do While userInput = ""

userInput = InputBox("Enter your name:")

Loop

MsgBox "Hello, " & userInput

End Sub

Warning: Always include an exit condition to prevent infinite loops!

Do Until Loop

Repeats code until a condition becomes true. Opposite logic of Do While.

Basic Syntax:

Do Until condition

' Code to repeat

Loop

Example 1: Fill Until Blank

Sub FillSequence()

Dim row As Long

row = 1

Do Until Cells(row, 1).Value = ""

Cells(row, 2).Value = row * 10

row = row + 1

Loop

End Sub

Example 2: Import Until End of File

Sub ReadFile()

Dim row As Long

Dim textline As String

row = 1

Open "C:\data.txt" For Input As #1

Do Until EOF(1) ' End Of File

Line Input #1, textline

Cells(row, 1).Value = textline

row = row + 1

Loop

Close #1

End Sub

Do While vs Do Until:

Do While:

Loop continues WHILE condition is True

Do While x < 10

Do Until:

Loop continues UNTIL condition is True

Do Until x = 10

Loop Variations

Do...Loop While (Check at End)

' Code executes at least once

Loop While condition

Condition checked AFTER first iteration. Guarantees at least one execution.

Do...Loop Until (Check at End)

' Code executes at least once

Loop Until condition

Similar to Do While, but with Until logic at the end.

Nested Loops

Place one loop inside another to work with multi-dimensional data.

Example: Fill 2D Grid

Sub FillGrid()

Dim row As Integer, col As Integer

For row = 1 To 10 ' Outer loop

For col = 1 To 5 ' Inner loop

Cells(row, col).Value = row * col

Next col

Next row

End Sub

Creates a 10x5 multiplication table

Important: Inner loop completes all iterations for each single iteration of outer loop.

Loop Control Statements

Exit For / Exit Do

Immediately exit loop when condition met.

For i = 1 To 1000

If Cells(i, 1).Value = "STOP" Then

Exit For ' Break out of loop

End If

Next i

Continue (VBA Workaround)

VBA doesn't have Continue, but use If to skip iterations:

For i = 1 To 10

If i Mod 2 = 0 Then ' Skip even numbers

' Don't process

Else

Debug.Print i ' Only odd numbers

End If

Next i

Loop Best Practices

✓

Use For Each for collections: Cleaner and faster than For Next with index

✓

Always include exit conditions: Prevent infinite loops in Do While/Until

✓

Minimize worksheet interactions: Read to array, loop array, write back to worksheet

✓

Use Long for row counters: Integer maxes at 32,767

✓

Declare loop variables: Use Dim with specific types

✓

Add status updates: For long loops, show progress to user

✓

Turn off screen updating: Application.ScreenUpdating = False before loops

Common Loop Mistakes

❌ Infinite Loop

Do While x < 10

' Forgot to increment x!

Loop

Always modify the loop variable or condition inside the loop.

❌ Modifying Collection While Looping

For Each ws In Worksheets

ws.Delete ' Don't modify collection!

Next ws

Loop backwards or use For Next when deleting items.

❌ Inefficient Worksheet Access

For i = 1 To 10000

Cells(i, 1).Value = Cells(i, 2).Value * 2 ' Slow!

Next i

Use arrays for large data operations instead of cell-by-cell access.

Generate Loop Code Automatically

Let AI write your VBA loops with proper syntax and best practices

✓ No credit card required ✓ 5 free generations

More from this guide:

VBA Loops: For, Do While, Do Until

Quick Reference

Understanding VBA Loops

For Loops

Do Loops

For...Next Loop

Basic Syntax:

Example 1: Loop 1 to 10

Example 2: Loop Through Rows

Example 3: Using Step (Count by 2s)

Example 4: Countdown (Negative Step)

For Each...Next Loop

Basic Syntax:

Example 1: Loop Through Range

Example 2: Loop Through Worksheets

Example 3: Loop Through Array

Do While Loop

Basic Syntax:

Example 1: Find Last Row

Example 2: Loop Until Value Found

Example 3: User Input Loop

Do Until Loop

Basic Syntax:

Example 1: Fill Until Blank

Example 2: Import Until End of File

Do While vs Do Until:

Loop Variations

Do...Loop While (Check at End)

Do...Loop Until (Check at End)

Nested Loops

Example: Fill 2D Grid

Loop Control Statements

Exit For / Exit Do

Continue (VBA Workaround)

Loop Best Practices

Common Loop Mistakes

❌ Infinite Loop

❌ Modifying Collection While Looping

❌ Inefficient Worksheet Access

Related VBA Tutorials

Excel VBA Guide

Edit VBA Code

SUMIF Function

Find Duplicates

Generate Loop Code Automatically