An Excel VBA loop repeats a block of code until it has processed a set number of items or a condition is met. Use For...Next for a known number of repetitions, For Each...Next to visit cells or other objects, and Do While or Do Until when the stopping point depends on a condition.
What a VBA loop does
A loop lets a macro carry out the same instructions repeatedly without copying the instructions into the code again and again. For example, a macro can check every row in a table, fill cells in a selected range, or process each worksheet in a workbook. The loop determines when to repeat and when to move on.
Most loops have three practical parts: the work to perform, the rule for continuing, and a way to make progress toward stopping. In a counter-based loop, the counter changes on each pass. In a condition-based loop, the code must update something that eventually makes the condition false. If that change never happens, the macro can run indefinitely.
VBA runs in desktop Excel. Excel for the web cannot create, run, or edit VBA macros; open the workbook in the desktop application to use them. The examples below are for Excel for Microsoft 365 on Windows, though the VBA language is also available in Excel for Mac.
Use For…Next when the number of passes is known
A For...Next loop is a straightforward choice when the number of repetitions is fixed or can be calculated before the loop starts. Its counter starts at the first value and advances toward the ending value. By default, it increases by one; add Step to use a different increment or count down.
This macro writes the numbers 1 through 10 into cells A1:A10 on the active worksheet:
Sub FillNumbers()
Dim i As Long
' Write one number to each row.
For i = 1 To 10
Cells(i, 1).Value = i
Next i
End Sub
Long is generally a suitable type for row counters because a worksheet can contain far more rows than fit in an older 16-bit Integer. The line Next i advances the counter and returns execution to the loop. When the counter passes the ending value, VBA continues with the statement after Next.
To count down from 10 to 1, write For i = 10 To 1 Step -1. A loop can also use a calculated upper limit, such as a variable holding the last row number. Be careful when changing the counter inside the loop: doing so can make the sequence harder to follow and can cause rows to be skipped or processed unexpectedly.
Use For Each to visit cells and objects
For Each...Next is useful when the items already form a collection, such as the cells in a range or the worksheets in a workbook. Rather than calculating an index for each item, VBA assigns the next object in the collection to a variable. This often makes code easier to read when the task is “do this for every cell in this range.”
The example colors nonempty cells in A2:A20 on a worksheet named Data:
Sub HighlightNonemptyCells()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Data")
' Visit each cell in the specified range.
For Each cell In ws.Range("A2:A20")
If Not IsEmpty(cell.Value) Then
cell.Interior.Color = vbYellow
End If
Next cell
End Sub
Here, cell is declared as a Range, and the worksheet is qualified through ws. That qualification matters: unqualified references such as Cells or Range can refer to the active worksheet, which may not be the one the macro is meant to change. A cell containing a formula that displays an empty string is not truly empty, so IsEmpty will still treat it as occupied.
You can also loop through workbook worksheets with a variable declared As Worksheet. Use that pattern when the task applies to every sheet, but consider excluding sheets such as instructions or summaries if they should not be changed.
Use Do While or Do Until for condition-based repetition
A Do While loop repeats as long as its condition is true. A Do Until loop repeats until its condition becomes true. These are appropriate when the number of passes is not known in advance—for example, when checking records until a stopping marker is found.
This small example prints the numbers 1 through 5 in the VBA editor’s Immediate window:
Sub CountWithDoWhile()
Dim n As Long
n = 1
Do While n <= 5
Debug.Print n
n = n + 1
Loop
End Sub
The counter update, n = n + 1, is essential. Without it, n <= 5 remains true and the loop does not finish. The condition is checked before each pass, so if it is false at the start, the loop body does not run.
VBA also allows the condition at the end, as in Do followed by the body and then Loop While condition. In that form, the body runs at least once before the condition is checked. Choose the top-tested form when the code should do nothing if the condition is already false; use the bottom-tested form only when one pass is required.
Loop through worksheet rows with a clear boundary
A common Excel task is to examine rows below a header. One way to find the last occupied row in column A is Cells(Rows.Count, "A").End(xlUp).Row. This starts at the bottom of the worksheet and moves upward to the last nonempty cell in that column. The result depends on the chosen column, so use one that is reliably populated for each record.
This example assumes row 1 is a header, column A contains a record name or identifier, and column B is available for a status. It writes “Reviewed” beside each nonblank entry in column A, up to the last used row in that column:
Sub MarkRecordsReviewed()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Start below the header and check each record row.
For r = 2 To lastRow
If Len(ws.Cells(r, "A").Value) > 0 Then
ws.Cells(r, "B").Value = "Reviewed"
End If
Next r
End Sub
This code overwrites existing values in column B for rows that meet the condition, so check that the destination is clear or suitable before running it. If column A has no records below the header, lastRow is 1 and the loop has no rows to process. If the data can contain blank rows, this method still checks each row up to the last nonempty cell; it does not stop at the first blank.
Nested loops and controlling a loop
A nested loop is a loop inside another loop. It is useful when each item in one group must be paired with every item in another—for instance, checking several columns for each row. If an outer loop runs 100 times and an inner loop runs 10 times for each pass, the body of the inner loop runs 1,000 times. That multiplication is important when estimating how long a macro may take.
Keep nested loops limited to cases that need them. If you only need to process each cell in a rectangular range, a single For Each loop may be clearer. Give counters distinct names, such as rowNum and colNum, so it is easy to see which loop controls which dimension.
Exit For leaves the nearest For loop, and Exit Do leaves the nearest Do loop. These can stop a search as soon as a match is found. Use them deliberately: leaving a loop early is not the same as finishing every item, and code after the loop will run either way.
Choose a loop and keep macros efficient
- Choose
For...Nextwhen you know the number of iterations or have a start and end index. - Choose
For Each...Nextwhen you want to visit every object in a range or collection. - Choose
Do WhileorDo Untilwhen a condition, rather than a predetermined count, controls when to stop. - Use a nested loop only when the task genuinely needs combinations or multiple dimensions.
For a small range, reading or writing one cell at a time is often simple and adequate. For a very large range, repeated communication between VBA and the worksheet can become a bottleneck. A common optimization is to read a range into a VBA array, process the values in memory, and write the results back in a batch. That approach can be faster, but it requires careful handling of range dimensions, empty cells, and data types. Start with clear code, measure whether speed is actually a problem, and optimize the part that takes time.
Avoid using Select and Activate just to reach cells. Refer to the intended worksheet and range directly, as the examples do. Direct references reduce dependence on which sheet or cell happens to be active and make a macro more predictable.
Run, debug, and protect your workbook
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste or type the procedure into the module, and return to Excel. Run a macro from Developer > Macros, or press Alt+F8 to open the macro list. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.
On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well. You can also use the Developer tab and its Visual Basic or Macros commands. Keyboard mappings and ribbon availability can vary with the keyboard and Excel setup. Excel for the web does not run VBA, so use desktop Excel for these steps.
To investigate a loop, set a breakpoint by clicking beside a code line in the editor, then run the macro. Step through the procedure and watch the counter or cell values change. The Immediate window can display values printed with Debug.Print. If a macro seems stuck, use the editor’s reset or break controls rather than repeatedly starting another copy of it.
Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even if the macro changes many cells. Save workbooks containing VBA as Excel Macro-Enabled Workbook (.xlsm) so the code can be retained. Only enable macros in files you trust; macros can make extensive changes to a workbook and may execute code beyond the visible sheet.
Frequently Asked Questions
What is the easiest VBA loop for a beginner?
For a fixed count, start with For...Next; it makes the start, finish, and counter visible. For every cell in a specified range, For Each...Next is often the clearest option.
What is the difference between For Each and For Next?For Each visits each object in a collection, such as each cell in a range. For...Next advances a numeric counter through a sequence, which is useful when the index itself matters.
How do I stop an infinite VBA loop?
First interrupt or reset the running macro in the Visual Basic Editor. Then check that the loop’s counter or condition changes on every required pass and that the exit condition can become true. Save a copy before rerunning the corrected macro.
Can I break out of a VBA loop early?
Yes. Use Exit For inside a For loop or Exit Do inside a Do loop. They exit the innermost loop of that type, after which execution continues with the statement following it.
Can Excel for the web run VBA loops?
No. VBA macros must be run in desktop Excel for Windows or Mac. Excel for the web can open many macro-enabled workbooks, but it does not run or edit their VBA code.
Related
- How to Merge Cells in Excel Without Losing Data Safely
- Excel Table: Make a Plain Data-Only Table From Your Data
- Import Excel CSV Files in Python: A Step-by-Step Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA AutoFilter: Examples, Criteria, and FixesExcel VBA AutoFilter filters a worksheet range or Excel table by applying criteria to one or more columns.…
- Excel VBA Global Array Example: Scope, Setup, and UseIn Excel VBA, declare a shared array with a Public statement at the top of a standard module,…
- VBA Range.Intersect: Find Overlapping CellsIn VBA, use Application.Intersect(range1, range2) to find the cells shared by two or more ranges. It returns a…
- VBA Function Structure: Syntax, Examples, and ScopeA VBA function is a procedure that performs work and returns a value. Its basic structure is Function…
- VBA Code to Insert an Image in an Excel Cell AutomaticallyUse VBA’s Shapes.AddPicture method to insert a picture, then position and size it to match a worksheet cell.…
- Excel VBA FindNext: How to Find the Next MatchIn Excel VBA, use Range.FindNext to move from one match to the next after starting a search with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.