An Excel VBA loop statement repeats a block of code, either for a set number of times or while a condition is true. Use For...Next for a counted sequence, For Each...Next to visit items in a collection or range, and Do...Loop when repetition depends on a condition.
What a VBA loop does
A macro without a loop generally performs its instructions once. A loop lets the same instructions process many cells, rows, worksheets, or other objects without copying the code for every item. For example, a macro can examine each value in a column, format every matching cell, or apply the same calculation to a block of records.
Every loop has a body: the statements between its opening and closing keywords. The loop also needs a way to determine whether it should continue. In a counted loop, VBA advances a counter; in a condition-based loop, it evaluates an expression. Choosing the right kind matters because a loop that never reaches its stopping point can keep a macro running indefinitely.
Before running a macro that changes workbook data, save a separate copy of the workbook. Macro changes generally cannot be reversed with Ctrl+Z, so a backup is the simplest way to protect the original data.
Use For…Next for a known number of repetitions
For...Next is useful when you know the number of iterations or want to process a sequence of row or column numbers. The counter starts at the value after =, and VBA runs the body before advancing it toward the ending value.
Sub NumberRows()
Dim rowNum As Long
' Write a sequence into B2:B6.
For rowNum = 2 To 6
Cells(rowNum, 2).Value = rowNum - 1
Next rowNum
End Sub
In this example, VBA writes 1 in B2, 2 in B3, and continues through 5 in B6. The ending value is included when the counter reaches it. The counter variable is declared as Long, a suitable choice for row numbers because a worksheet can contain far more rows than fit in an Integer variable.
You can specify an increment with Step. For instance, For rowNum = 2 To 10 Step 2 visits 2, 4, 6, 8, and 10. A negative step counts downward, as in For rowNum = 10 To 2 Step -2. Ensure the step points toward the ending value; otherwise, the loop may not run as intended.
Use For Each to visit cells and objects
For Each...Next is often the clearest choice when the task is “do this for every item in this group.” It can iterate through objects in collections, including worksheets in a workbook and cells in a range. Unlike a numbered loop, it does not require you to calculate the next row number yourself.
This example checks the currently selected cells and colors numeric values below zero. Select a range before running it; the procedure is designed for a cell selection.
Sub HighlightNegativeCells()
Dim cell As Range
' Check each selected cell before formatting it.
For Each cell In Selection
If IsNumeric(cell.Value) Then
If cell.Value < 0 Then
cell.Interior.Color = vbRed
End If
End If
Next cell
End Sub
The declared variable, cell, represents one range object at a time. The IsNumeric check avoids comparing text directly with zero. The example uses Selection, so it relies on what is selected in the active Excel window; for a reusable production macro, a clearly specified worksheet and range can be safer than relying on the user’s current selection.
A similar pattern can walk through worksheets using a variable declared as Worksheet. This is helpful for applying consistent formatting or checking a named cell on every sheet. Be cautious if the macro adds or deletes items while it is iterating a collection, because changing that collection during the loop can make the sequence harder to reason about.
Use Do…Loop when a condition controls repetition
A Do...Loop is appropriate when the number of repetitions is not known in advance. Its condition can be checked before the body with Do While or Do Until, or after the body with Loop While or Loop Until. A condition at the top means the body might run zero times; a condition at the bottom means it runs at least once.
This procedure processes rows starting at row 2 and stops when column A is blank. It writes a value 10 percent higher in column B. The blank cell is the stopping rule, so the data should be contiguous in column A.
Sub AddTenPercentUntilBlank()
Dim ws As Worksheet
Dim rowNum As Long
Set ws = ActiveSheet
rowNum = 2
' Stop when column A has no value.
Do While Len(ws.Cells(rowNum, 1).Value) > 0
ws.Cells(rowNum, 2).Value = ws.Cells(rowNum, 1).Value * 1.1
rowNum = rowNum + 1
Loop
End Sub
The row number increases on every pass, allowing the loop eventually to reach a blank cell if one exists below the data. If there is no blank cell in the direction of travel, the loop can continue beyond the intended records. For data that may contain gaps, use a known last row or add another explicit stopping check rather than assuming the first blank marks the end.
How to choose the right loop
Start by asking whether the set of items is known and how the code should decide to stop. A practical guide is:
- Choose
For...Nextwhen a numeric counter or fixed range of indexes defines the work, such as rows 2 through 100. - Choose
For Each...Nextwhen you need to visit every member of a collection or every cell in a range. - Choose
Do WhileorDo Untilwhen a condition, such as encountering a blank value, governs how long the process continues.
These options are not interchangeable in every situation. A counted loop makes the expected number of iterations visible, which can make boundary checks easier. A For Each loop avoids manual index management when you simply need each object. A Do loop expresses a stopping condition directly, but it places more responsibility on the author to ensure that the condition can eventually change.
For a fixed worksheet range, either a row-number loop or a cell loop may work. Use the row-number form when the row index is needed for related work in several columns. Use a cell loop when the operation applies to each cell itself, such as checking or formatting each selected value.
Nested loops and stopping a loop early
A nested loop is a loop inside another loop. It can handle two-dimensional work, such as checking each cell in a grid: the outer loop can move through rows while the inner loop moves through columns. Keep the inner loop’s start, end, and counter distinct from the outer loop’s, or the counters can interfere with one another.
VBA also provides Exit For and Exit Do to leave the current loop early. For example, a search macro may stop once it finds a match instead of checking every remaining cell. Use these statements when the early exit is part of the intended logic, and make the reason for leaving clear with a comment or a well-named condition. An exit statement only leaves the loop it is inside; it does not automatically exit an enclosing loop or the whole procedure.
For nested loops, leaving the inner loop does not by itself stop the outer loop. The outer loop will proceed to its next iteration unless you add a separate condition or another control-flow approach. This distinction is a common source of macros that appear to stop but continue processing other rows or groups.
Common loop problems and how to prevent them
The most frequent errors come from an incorrect boundary, an unchanged condition, or an assumption about the data. Check these points when a macro skips records, processes too many cells, or seems not to finish:
- Off-by-one boundaries: Confirm whether the first and last row should be included. In a
For rowNum = 2 To 6loop, both 2 and 6 are processed. - Counter not updated: A condition-based loop must change something that affects its condition. If the row number or tested value never changes, the loop can run forever.
- Wrong active sheet: Unqualified references such as
Cells(rowNum, 1)refer to the active sheet. Qualify references with a worksheet variable when the macro must operate on a particular sheet. - Unexpected blanks or text: Decide how the code should handle empty cells, error values, and text before applying arithmetic or comparisons to worksheet values.
- Changing the collection: Deleting rows while looping through a range can shift the remaining items. A common strategy is to process rows from the bottom upward when deletions are required.
When a macro behaves unexpectedly, step through it in the Visual Basic Editor and inspect the counter and tested values at each iteration. On Windows, F8 steps through VBA code. On a Mac, the corresponding key may require Fn+F8, depending on keyboard settings; the Visual Basic Editor is also available from Excel’s Developer tab when that tab is enabled. A breakpoint can pause execution at a chosen line so you can examine the state before the loop continues.
Speed, readability, and safer workbook references
A loop over a large range can take time, particularly if the code repeatedly reads or writes individual worksheet cells. Keep the loop’s purpose narrow, avoid unnecessary recalculation or formatting inside each iteration, and make sure the loop does not scan far beyond the actual data. For substantial data processing, VBA can often read a range into an array, process the values in memory, and write results back in a block; that approach is more advanced and should be used only when the simpler loop is too slow or insufficient.
Make worksheet references explicit when the target matters. For example, assigning a worksheet to a variable and using ws.Cells(rowNum, 1) makes it clear which sheet the loop uses, even if the user changes the active sheet. Also declare each counter and object variable, use descriptive names, and indent the body consistently. Those habits make it easier to spot a missing increment, misplaced Next, or condition that can never become false.
To run VBA on Windows, open the Visual Basic Editor from Developer > Visual Basic, insert a standard module, and place a procedure there. The Developer tab may need to be enabled in Excel Options. On Mac, use the Visual Basic Editor from the Developer tab where available; ribbon and keyboard details can differ by keyboard configuration. Excel for the web can open a workbook containing VBA, but it does not run VBA macros; use the desktop Excel application for these procedures.
Frequently Asked Questions
What is the simplest loop in Excel VBA?
A basic For...Next loop is often easiest when you know the start and end values. For example, For i = 1 To 5 repeats its body five times if the counter is declared and the loop is closed with Next i.
What is the difference between For Next and For Each?For...Next advances a numeric counter through a sequence. For Each...Next assigns each item in a collection or range to an object variable, which is convenient when the item itself matters more than its position.
Can a Do While loop run zero times?
Yes. With Do While condition at the top, VBA checks the condition before entering the body. If the condition is false at the start, none of the body runs. A condition checked after the body allows one initial pass.
How do I stop an infinite VBA loop?
If Excel is still responsive, press Esc or Ctrl+Break on Windows to interrupt execution; keyboard layouts and Mac behavior can vary. Then inspect the loop condition and confirm that the value it depends on changes on every required pass. Save a copy before running macros that modify workbook content.
Can Excel for the web run VBA loops?
No. Excel for the web can store and open a workbook with VBA, but VBA macros must be run in desktop Excel. Excel for Microsoft 365 on Windows and the Mac desktop app support VBA, although some interface details differ.
Related
- Excel Split Cell Content Into Columns: Methods That Work
- Excel Split Cell Contents by Delimiter
- Excel Split Cell Based on Character: Methods That Work
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Can Excel VBA Read Data From a PivotTable?Yes. Excel VBA can read a PivotTable’s values and labels through the PivotTable object model, using methods such…
- VBA Split Function: Syntax, Examples, and Common UsesIn VBA, Split divides a text string wherever a chosen delimiter appears and returns the pieces as a…
- Excel VBA: Find a Cell with Specific Text in a RangeTo find a cell containing specific text with Excel VBA, use the Range.Find method. Set options such as…
- VBA Dictionary For Each: Loop Through Keys and ItemsIn VBA, use For Each key In dict.Keys to loop through a Scripting.Dictionary’s keys, then read each value…
- Excel VBA UserForm Animation Effects: What Really WorksExcel VBA UserForms have no built-in animation setting for effects such as fading, sliding, or zooming. You can…
- VBA: Filter a PivotTable by Cell Value AutomaticallyTo filter an Excel PivotTable from a cell, use VBA to read that cell and assign its value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.