Copy or Export this Code to VBA
Use these verified developer utilities to run this lesson directly inside your Microsoft Excel Visual Basic Editor.

In this article
- Understanding Nested Loop Execution Flow
- The Golden Rule of Loop Speed: Reading Ranges into Memory Arrays
- Frequently Asked Questions
- How do you exit an inner loop early without stopping the outer loop?
- How many nested loop levels can VBA support?
- What screen updating settings should be applied during heavy loops?
- Continue learning
In Chapter 27 of our Excel VBA series, we tackle nested loops—placing one loop inside another to traverse multi-dimensional data grids, compare cross-table matrices, and automate complex report consolidations. We also cover critical optimization practices that prevent nested loops from freezing Excel.
Understanding Nested Loop Execution Flow
In a nested loop, the inner loop runs through its complete iteration cycle for every single step of the outer loop. For example, cycling through 100 rows and 10 columns produces 1,000 total cell operations:
Sub LoopThroughGrid()
Dim r As Long, c As Long
' Outer loop: Rows 1 to 10
For r = 1 To 10
' Inner loop: Columns 1 to 5
For c = 1 To 5
Cells(r, c).Value = "R" & r & "C" & c
Next c
Next r
End Sub
The Golden Rule of Loop Speed: Reading Ranges into Memory Arrays
Direct cell-by-cell interaction (e.g. Cells(r, c).Value) is the #1 cause of slow macros. Transferring the entire range into an in-memory 2D Variant array speeds up nested loops by over 100x:
Sub FastMemoryNestedLoop()
Dim dataArr As Variant
Dim r As Long, c As Long
Dim rowCount As Long, colCount As Long
' 1. Read entire 10,000 row table into RAM at once
dataArr = Range("A1:E10000").Value
rowCount = UBound(dataArr, 1)
colCount = UBound(dataArr, 2)
' 2. Loop in memory (nanoseconds per iteration)
For r = 2 To rowCount
For c = 2 To colCount
If IsNumeric(dataArr(r, c)) Then
dataArr(r, c) = dataArr(r, c) * 1.05 ' Apply 5% inflation markup
End If
Next c
Next r
' 3. Write modified data back to the sheet in a single instant
Range("A1:E10000").Value = dataArr
End Sub
Frequently Asked Questions
How do you exit an inner loop early without stopping the outer loop?
Use Exit For inside the inner loop. It immediately transfers control back to the outer loop without breaking the outer iteration.
How many nested loop levels can VBA support?
While VBA supports deeply nested loops, good programming practice limits nesting to 2 or 3 levels. Deeper nesting should be refactored into modular helper functions for maintainability.
What screen updating settings should be applied during heavy loops?
Always turn off screen repainting and automatic calculation before executing heavy loops: Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual, restoring them upon completion.
📚 Master high-speed macros in our Excel VBA Hub.
Stuck on a step? Ask a question and the AI answers using this article.