To loop through every cell in an Excel range with VBA, use a For Each loop and a Range variable: For Each cell In ws.Range("A1:A10").Cells. Inside the loop, refer to the current cell as cell to read, test, or change its value.
A basic For Each loop
This example visits cells A2 through A20 on a worksheet and prints each cell’s address and value in the Immediate window. It uses ThisWorkbook so the worksheet reference belongs to the workbook containing the macro, rather than whichever workbook happens to be active.
Before running a macro that changes a workbook, save a separate copy: VBA changes cannot be reversed with Ctrl+Z. To see the output from this read-only example, open the Visual Basic Editor and show the Immediate window. On Windows, Alt+F11 opens the editor; on a Mac, the shortcut is usually Option+F11, though some keyboards require Fn as well.
Sub ListRangeCells()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each cell In ws.Range("A2:A20").Cells
Debug.Print cell.Address(False, False), cell.Value
Next cell
End Sub
Change Sheet1 and A2:A20 to match your workbook. The Cells property makes explicit that the loop should visit individual cells. For a single-column or single-row range, omitting .Cells will often appear to work, but including it makes the intention clearer and avoids confusion when the range expression changes.
Choose and qualify the range
A range can be a fixed address, a named range, a table column, or a range calculated while the macro runs. The key is to identify its worksheet. A bare reference such as Range("A1:A10") is tied to the active sheet, which can make a macro act on the wrong sheet if the user changes the selection. Qualifying it as ws.Range("A1:A10") makes the target explicit.
To loop through all currently used cells on a sheet, you can use ws.UsedRange.Cells. However, UsedRange can include cells that were formatted or edited in the past, even if they look empty now. For a data list in column A with a header in row 1, a last-row calculation is often more suitable:
Sub LoopToLastRow()
Dim ws As Worksheet
Dim cell As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For Each cell In ws.Range("A2:A" & lastRow).Cells
Debug.Print cell.Address(False, False), cell.Value
Next cell
End Sub
This method finds the last nonempty cell by moving upward from the bottom of column A. It assumes column A is the column that defines the extent of the records. If A is blank below the last row of data, or contains gaps, consider which column reliably indicates that a record exists. Also handle an empty data set deliberately: if there is only a header, the calculated range may not represent any data rows, so check lastRow before starting the loop.
You can also loop through a rectangular range such as ws.Range("B2:E50").Cells. The loop visits every cell in that area, moving across each row and then continuing through the next row. A multi-area range, such as a selection made with Ctrl-click, can be handled too, but code that depends on a particular shape is easier to understand when each area is processed explicitly.
Read, test, and change cell values
Inside a loop, cell.Value or cell.Value2 refers to the current cell’s contents. Value2 avoids the special Currency and Date subtypes used by Value; dates are generally exposed as their underlying numeric serial values. Use Value if you specifically need those subtypes, and remember that the displayed format is a separate matter from the stored value.
This example marks empty cells in a range. It checks for Excel errors before converting the value to text, because attempting to process an error value as ordinary text can cause a VBA type mismatch.
Sub MarkEmptyCells()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each cell In ws.Range("B2:B50").Cells
If Not IsError(cell.Value2) Then
If Len(CStr(cell.Value2)) = 0 Then
cell.Value = "Missing"
End If
End If
Next cell
End Sub
This treats a truly empty cell and a formula returning an empty string as empty, because both produce a zero-length string in this test. A cell containing spaces is not treated as empty. If spaces should count as blank, test a trimmed string instead, taking care to preserve numbers or other data if the macro should not convert them to text. If formulas must remain untouched, check cell.HasFormula before assigning a new value: writing to a formula cell replaces its formula.
For a numerical condition, use a separate error check and a numeric test before comparing. That is safer than assuming every cell contains a number. When the macro writes to cells, confirm that the target range is correct and that overwriting existing values is intended.
Loop by row or column
A single For Each cell loop is usually the simplest choice when every cell gets the same treatment. Nested loops are useful when the logic depends on a row, a column, or the cell’s position. For example, you can loop over area.Rows, then over the cells in each row, to process a small rectangular block one record at a time.
For position-dependent work, an indexed loop makes row and column numbers visible. In the example below, the macro checks the first three columns of rows 2 through 20 and reports the address of numeric cells. It does not change the worksheet.
Sub CheckBlockByPosition()
Dim ws As Worksheet
Dim rowNum As Long
Dim colNum As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
For rowNum = 2 To 20
For colNum = 1 To 3
If IsNumeric(ws.Cells(rowNum, colNum).Value2) Then
Debug.Print ws.Cells(rowNum, colNum).Address(False, False)
End If
Next colNum
Next rowNum
End Sub
Use For Each when you only need the current cell object; use indexed loops when the row or column number matters or when you need to construct a related address. Neither approach is automatically faster for every task. Clarity and the size of the range are good starting points when choosing.
Improve speed on larger ranges
Looping through a few dozen cells is generally straightforward. A loop over tens of thousands of cells can take noticeably longer, especially if every iteration reads from or writes to the worksheet separately. Excel must communicate with the worksheet object model for those operations, so repeated cell-by-cell interaction can be the bottleneck.
For larger data sets, a common approach is to read a rectangular range into a VBA array, process the values in memory, and write the results back in one operation. A multi-cell range’s Value2 property returns a two-dimensional array. The code then uses array indexes rather than repeatedly accessing each worksheet cell. When using this pattern, preserve the same range dimensions when writing the array back, and make sure the array contains the results you intend to replace the original data with.
Another common speed technique is to temporarily turn off screen updating or automatic calculation during a long macro, then restore the original settings afterward. Do not leave those settings disabled if the macro stops with an error. Save their initial values and restore them in a cleanup section, including when an error occurs. Avoid adding this complexity to a short loop that already runs quickly; it makes the macro harder to maintain and can confuse users if Excel is left in an unexpected state.
Common mistakes and how to avoid them
- Using the active sheet accidentally: qualify the range with a worksheet variable, such as
ws.Range("A1:A10"), rather than relying on the selected sheet. - Changing a formula unintentionally: assigning to
cell.Valuereplaces the cell’s formula with a value. CheckHasFormulaor choose a separate output range when formulas need to be preserved. - Assuming every value is ordinary text or a number: cells may contain errors, formulas, dates, or blanks. Check the value’s type before applying operations that require a particular type.
- Using the wrong loop bounds: a fixed address can miss new records, while an overly broad address wastes time. Choose a reliable last-row column or use a well-defined Excel table.
- Running a macro in the wrong workbook:
ThisWorkbookmeans the workbook that contains the VBA code;ActiveWorkbookmeans the workbook currently active in Excel. They can be different. - Expecting Ctrl+Z to undo a macro: Excel clears its regular undo history when VBA runs. Save a backup copy before running a macro that makes changes.
If you get a “Subscript out of range” error on the worksheet assignment, check that the sheet name matches exactly and that the sheet belongs to the workbook referenced by ThisWorkbook. If a loop runs but changes nothing, verify the range address, the conditions inside the loop, and whether the macro is pointing at the intended workbook.
When to use VBA, and when not to
VBA is useful when a repeatable task needs to inspect cells and then take conditional actions, such as flagging exceptions, copying selected records, or applying a sequence of changes that ordinary worksheet formulas do not perform conveniently. A macro can also be assigned to a button for repeated use by workbook users.
For calculations that should update automatically as source cells change, worksheet formulas are often easier to audit and maintain. Excel tables, filters, conditional formatting, and Power Query can also handle many tasks without a cell-by-cell macro. If the job is simply to calculate a value for each row, a formula in a table column may be clearer than VBA. Pick VBA when the task is genuinely procedural or needs automation, not merely because a loop is possible.
Excel for the web can open workbooks containing VBA, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for those tasks. Excel for Microsoft 365 on Windows supports the examples above; desktop Excel for Mac also supports VBA, though some features and object-model behavior can differ. Macro availability is also affected by security settings and organizational policy. Save a workbook containing VBA in a macro-enabled format such as .xlsm if the code needs to remain in the file.
Frequently Asked Questions
How do I loop through every used cell in a worksheet?
Set a worksheet variable, then use For Each cell In ws.UsedRange.Cells. Be aware that UsedRange can include cells with residual formatting or past edits, so a defined data range or table column may be more precise.
How do I loop through only nonblank cells?
Loop through the intended range and place the work inside a condition such as If Len(CStr(cell.Value2)) > 0 Then. Check IsError(cell.Value2) first if the range may contain Excel error values.
How do I loop through a column until the last filled row?
Find the last used row in a reliable column with ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, then construct a range from the first data row to that row. This assumes column A identifies the last record; choose another column if it does not.
Can I change cell values while looping?
Yes. Assign to cell.Value or another appropriate property inside the loop. Remember that writing to a formula cell replaces its formula, and the change cannot be undone with Ctrl+Z after the macro runs.
Can I run VBA loops in Excel for the web?
No. Excel for the web can preserve a workbook with VBA, but running or editing VBA requires desktop Excel. Use the Windows or Mac desktop application, subject to its macro security settings.
Related
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Using VBA to Sort Contracts in Excel: A Practical LessonTo sort a contract register by expiration date with VBA, put the records in one rectangular range with…
- How to Delete Blank Rows in Excel with a VBA MacroTo delete blank rows in Excel with a macro, select the data range, run a VBA procedure that…
- Excel VBA GoTo: Syntax, Examples, and Error HandlingIn Excel VBA, GoTo transfers execution to a named label elsewhere in the same procedure. It is most…
- Excel Goal Seek VBA Code for a Whole ColumnTo run Goal Seek down a column, loop through the populated rows and call Range.GoalSeek once per row:…
- Cannot Run Macro Error in Excel: Causes and SolutionsIf Excel says it cannot run a macro, first confirm that the macro exists in an open, macro-enabled…
- Excel VBA Range Copy: Examples, Fixes, and Worksheet TipsTo copy cells with Excel VBA, use the Range.Copy method and specify a destination range; this copies the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.