A loop in an Excel VBA macro repeats a block of instructions for a set of cells, rows, objects, or until a condition changes. Use For...Next for a known number of repetitions, For Each...Next to visit each item in a collection, and Do...Loop when repetition depends on a condition.
What a VBA loop does
VBA, or Visual Basic for Applications, is the macro language built into desktop Excel. A loop lets a macro apply the same operation repeatedly without writing a separate instruction for every row. For example, a macro can calculate totals down a column, check each cell for a particular value, or process every worksheet in a workbook.
Most loops have three parts: the instructions to repeat, a rule for continuing, and a way to reach the end. A counter-based loop changes a number each pass. A collection loop moves from one object to the next. A condition-based loop checks whether a stated condition is still true or has become true.
Loops are useful when the amount of work changes. A macro can determine the last populated row and process the current data, rather than containing a fixed list of hundreds of cell references. The loop does not decide whether the result is sensible, however; its range, condition, and actions still need to match the worksheet.
Use For…Next for a known range of repetitions
For...Next is a good fit when a macro can count the repetitions, such as processing rows 2 through the last data row. This example assumes the worksheet is named Orders, column A contains quantities, column B contains unit prices, and column C is where the macro should write line totals. It assumes the input cells contain numeric values or blanks.
Sub FillLineTotals()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
' Work only on the named worksheet in this workbook.
Set ws = ThisWorkbook.Worksheets("Orders")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Row 1 is a heading; process the data rows below it.
For rowNum = 2 To lastRow
If Len(ws.Cells(rowNum, "A").Value) > 0 Then
ws.Cells(rowNum, "C").Value = _
ws.Cells(rowNum, "A").Value * ws.Cells(rowNum, "B").Value
End If
Next rowNum
End Sub
Here, lastRow is found by starting at the bottom of column A and moving up to the last non-empty cell. The loop starts at row 2 to leave the heading alone. If column A contains no data below the heading, the start value is greater than the end value, so this ascending loop performs no iterations. If the macro’s data may have gaps or column A is not a reliable way to identify the final record, choose a different column or a different way to find the range.
The variable rowNum is the counter. VBA advances it by one each time through the loop unless a different step is specified. For example, For rowNum = 2 To 10 Step 2 visits 2, 4, 6, 8, and 10. The matching Next rowNum marks the end of the repeated block.
Use For Each to process cells or objects
Use For Each...Next when the goal is to visit every member of a collection, rather than to use the member’s row number as the main control. A worksheet range is a collection of cells, and the Worksheets collection contains a workbook’s sheets. For example, the following code changes nonblank names in A2:A20 to uppercase and writes the result in the adjacent cell.
Sub UppercaseNames()
Dim ws As Worksheet
Dim cell As Range
' Set the worksheet and visit each cell in the chosen range.
Set ws = ThisWorkbook.Worksheets("Orders")
For Each cell In ws.Range("A2:A20")
If Len(cell.Value) > 0 Then
cell.Offset(0, 1).Value = UCase(CStr(cell.Value))
End If
Next cell
End Sub
Offset(0, 1) means the same row and one column to the right. This example has a deliberately fixed range; to work with a changing list, calculate the last row first and build the range from that value. A For Each loop is also useful for worksheets: declare a variable ws As Worksheet, then use For Each ws In ThisWorkbook.Worksheets to perform an operation on every sheet.
When changing cells, iterating over a range is often clearer than incrementing a row counter. If the macro needs to move backward through a collection while deleting items, take extra care: removing an item can shift the remaining items and cause one to be skipped. A reverse-counting For...Next loop is often safer for that particular job.
Use Do…Loop when a condition controls the end
A Do...Loop is appropriate when the number of passes is not known in advance but a condition determines when to stop. For example, a macro might continue while a counter is within the data range. Initialize and update the counter so the condition can eventually become false.
Sub MarkLargeAmounts()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
' Find the final data row and start below the heading.
Set ws = ThisWorkbook.Worksheets("Orders")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
rowNum = 2
' Continue while the counter identifies a data row.
Do While rowNum <= lastRow
If IsNumeric(ws.Cells(rowNum, "B").Value) Then
If ws.Cells(rowNum, "B").Value > 1000 Then
ws.Cells(rowNum, "D").Value = "Review"
End If
End If
rowNum = rowNum + 1
Loop
End Sub
This example checks values in column B and writes a review note in column D when a numeric value exceeds 1000. Do While checks its condition before running the body, so it can run zero times. Do Until also checks before the body but continues until its condition becomes true. VBA also allows the condition after the loop body; that form always runs the body at least once, so use it only when that behavior is intended.
How to choose the right loop
Choose the loop whose stopping rule most directly describes the task. A complicated loop is not automatically more powerful; it is often just harder to review. These are practical starting points:
- Use For…Next when you know the starting and ending numbers, especially when each pass corresponds to a row or a fixed index.
- Use For Each…Next when you want to visit every cell, worksheet, workbook object, or other collection member.
- Use Do While or Do Until when a changing condition, rather than a predetermined count, controls how long to continue.
- Use a nested loop only when each item in one group needs to be paired with items in another group, such as checking every cell in a small rectangular range.
For example, a row loop can process each order once. A nested loop over every row and every column may be appropriate when inspecting a two-dimensional block, but it can do much more work than necessary if only one column matters. Narrowing the range and choosing a clear stop rule usually makes code easier to understand and less prone to mistakes.
Common loop problems and how to prevent them
An infinite loop occurs when a condition never changes in the direction needed to stop. In a Do While rowNum <= lastRow loop, forgetting rowNum = rowNum + 1 means the same row is processed repeatedly. Before running a condition-based macro, confirm that every path through the loop updates the value or condition that controls it.
Off-by-one errors are another frequent source of trouble. Starting at row 1 can overwrite a heading; ending one row too early can omit a record. Check the chosen first and last rows against a small sample of the actual sheet. Also remember that End(xlUp) identifies the last occupied cell in one specified column, not necessarily the last row containing anything anywhere on the worksheet.
Unqualified references can direct actions to the wrong sheet. For example, Cells(rowNum, 1) without a worksheet prefix refers to the active sheet. Qualifying it as ws.Cells(rowNum, 1) makes the target explicit and avoids relying on which sheet happens to be selected. The same principle applies to Range references.
Cell values may be blank, text, or Excel error values even when a macro expects numbers. Validate data where necessary before performing arithmetic or comparisons. A condition such as IsNumeric can help with numeric text and numbers, but error values require separate care; a simple example should not be treated as complete validation for every workbook.
Exit For can leave a For or For Each loop early, for example once a matching record is found. Use it when stopping early is part of the intended logic, not as a substitute for a sound loop boundary. For a Do loop, Exit Do provides a similar early exit.
Speed, workbook safety, and running a macro
For a small range, a straightforward loop is often easiest to maintain. For a very large range, repeatedly reading and writing individual worksheet cells can be slow because each interaction crosses between VBA and Excel. 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 much faster, but it requires careful attention to array bounds, data types, and preserving formulas or formatting as required.
To add or run VBA in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. In the editor, insert a standard module, place the procedure there, and run it from Excel or the editor. Save a workbook containing macros as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain its VBA code.
On Mac, Excel supports VBA, but the editor and shortcut behavior can differ; the Visual Basic Editor is available from the Developer tab, and some keyboards require the Fn key with function-key shortcuts. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Use desktop Excel for those tasks.
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z (on Mac, Command+Z), so a backup is the reliable way to recover from unintended edits. Run macros only from workbooks you trust, and review what a macro changes before enabling or executing it.
Frequently Asked Questions
What is the simplest loop in Excel VBA?
A For...Next loop is a straightforward choice when you know how many times to repeat an action. For example, For i = 1 To 10 repeats the enclosed instructions ten times, provided the loop variable is declared and the block ends with Next i.
How do I loop through every row with data?
Find the last used row in a dependable column, then use For rowNum = firstDataRow To lastRow. The right column and first data row depend on the worksheet layout. If the chosen column has blanks near the bottom, it may not identify all records correctly.
What is the difference between For and For Each in VBA?For...Next typically uses a counter that moves through numbers or indexes. For Each...Next visits each item in a collection, such as each cell in a range or each worksheet in a workbook. Use the form that makes the iteration and its stopping point easiest to see.
Can I stop a VBA loop early?
Yes. Use Exit For inside a For or For Each loop, and Exit Do inside a Do loop. Add a clear condition for the exit so readers can tell why the macro stops before reaching the normal end.
Can Excel for the web run VBA loops?
No. Excel for the web does not run VBA macros or provide the desktop Visual Basic Editor. Open the macro-enabled workbook in Excel desktop for Windows or Mac to edit and run VBA.
Related
- How to Add Rows to an Existing Group in Excel
- Add a Jigsaw Sudoku Solver to Excel
- Add Quadrants to an Excel Scatter Plot: Step-by-Step
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Recorder: Keyboard Shortcuts and SetupYes. In Excel for Microsoft 365 on Windows, record a macro from Developer > Code > Record Macro…
- Assign a Macro to a UserForm Command Button in ExcelIn Excel for Microsoft 365 on Windows, you do not assign a UserForm command button through the worksheet’s…
- Excel VBA Macro Best Practices for Reliable WorkbooksFor reliable Excel VBA macros, make each procedure do one clear job, declare variables with Option Explicit, qualify…
- Excel VBA Merge Range: How to Merge Cells Safely with VBATo merge a range with Excel VBA, call the Merge method on a Range object, for example Range("B2:D2").Merge.…
- How to Open VBA in Excel: Find and Use the VBA EditorTo open the VBA editor in Excel for Microsoft 365 on Windows, open a workbook and press Alt+F11.…
- Access VBA: How to Delete Table Rows Safely and CorrectlyIn Access VBA, a “row” is usually a record in a table. To delete matching records, run a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.