To loop through every cell in an Excel named range in VBA, assign the range to a Range variable and use For Each cell In target.Cells. The example below reads each cell in the worksheet-scoped name SalesInput and prints its address and value in the Immediate window.
Sub LoopNamedRange()
Dim target As Range
Dim cell As Range
' Use the worksheet that owns the named range.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("SalesInput")
' Visit each cell, including cells that are blank.
For Each cell In target.Cells
If IsError(cell.Value2) Then
Debug.Print cell.Address(False, False), "#ERROR"
Else
Debug.Print cell.Address(False, False), cell.Value2
End If
Next cell
End Sub
Before running any macro, save a copy of the workbook: changes made by VBA cannot be undone with Ctrl+Z. The Immediate window is available in the Visual Basic Editor, usually opened with Ctrl+G.
What a named-range loop visits
A named range is a defined name that refers to a cell or range, rather than a special kind of VBA collection. If SalesInput refers to Sheet1!$B$2:$B$20, the loop visits each cell in that address. The name makes code easier to understand than a hard-coded address, and it can keep working if the name’s reference is adjusted later.
The For Each loop assigns one cell at a time to the variable cell. That variable must be declared as a Range, and the named range itself is held in another Range variable called target. Using target.Cells makes it clear that the loop is over individual cells, not over rows or columns.
The sample uses Value2 to read the underlying cell value without the date and currency conversions associated with the Value property. It checks for an Excel error value before printing, because trying to treat an error such as #N/A as ordinary text or a number can cause a VBA type mismatch. Output appears in the Immediate window, not in the worksheet.
Choose the right name reference
Excel names can have workbook scope or worksheet scope. A workbook-scoped name is available throughout the workbook. A worksheet-scoped name belongs to one sheet, and another sheet can have a different local name with the same spelling. Excel’s Formulas > Name Manager shows each name’s scope and the address or formula it refers to.
- For a worksheet-scoped name: qualify the name through its worksheet, as in
ThisWorkbook.Worksheets("Sheet1").Range("SalesInput"). This makes the intended sheet explicit. - For a workbook-scoped name: use
ThisWorkbook.Names("SalesInput").RefersToRangeto retrieve the referenced range explicitly. - For a name on the active sheet:
ActiveSheet.Range("SalesInput")can be convenient, but it depends on which sheet is active when the macro runs.
ThisWorkbook means the workbook containing the VBA code. That is often safer than ActiveWorkbook, which means whichever workbook currently has focus and could be a different file. If the macro is stored in Personal.xlsb or an add-in, the workbook containing the code may not be the workbook containing the data; in that case, qualify the intended workbook deliberately.
For a workbook-level name, an explicit setup looks like this:
Sub LoopWorkbookName()
Dim target As Range
Dim cell As Range
' Retrieve the workbook-scoped name from this file.
Set target = ThisWorkbook.Names("SalesInput").RefersToRange
For Each cell In target.Cells
Debug.Print cell.Address(False, False), cell.Value2
Next cell
End Sub
Use the worksheet-qualified form when local scope matters. If a workbook-level and worksheet-level name share a spelling, an unqualified reference can resolve differently depending on context. Making the sheet or workbook explicit reduces surprises and helps someone reading the macro understand which range is intended.
Read values, skip blanks, or change cells
Many loops need to ignore empty cells rather than process every cell. A blank cell can have an empty value, while a formula that displays an empty string may also return an empty string through Value2. If both should count as empty for the task, test the value before acting on it. Remember that a cell containing spaces is not empty: trim text if spaces should be ignored.
For example, to print only nonblank, non-error entries, keep the error check before the blank check:
Sub PrintNonblankEntries()
Dim target As Range
Dim cell As Range
' Resolve the named range on Sheet1.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("SalesInput")
For Each cell In target.Cells
If Not IsError(cell.Value2) Then
If Len(Trim$(CStr(cell.Value2))) > 0 Then
Debug.Print cell.Address(False, False), cell.Value2
End If
End If
Next cell
End Sub
This example treats a value consisting only of spaces as blank. It converts non-error values to text for the test, so adapt the condition if the range contains data types that should be handled differently. For example, if zero is meaningful, this test preserves it because the text representation of zero has a nonzero length.
A loop can also write values, but writing to a cell may replace a formula with a constant. If the goal is to change displayed formatting, use a formatting property rather than assigning a new value. If the goal is to transform text, check that the cell contains text and is not an error before assigning the transformed result. A named range does not make edits reversible: VBA changes still cannot be undone with Ctrl+Z.
One cell, multiple cells, and multiple areas
A named range may refer to one cell or many. The same For Each pattern works for a single cell; it simply runs once. If the name refers to a multi-area range, such as several nonadjacent blocks, looping over target.Cells visits the cells in those areas. This is useful for selections assembled from separate sections, though it is worth confirming that the name actually refers to the intended areas.
For a contiguous rectangular range, you can also use row and column indexes when the position within the rectangle matters. For example, an indexed loop can distinguish the first row as a header or pair each value with a column. For Each is generally clearer when every cell receives the same treatment; indexed loops are more appropriate when the logic depends on row or column number.
Do not assume that a named range contains only visible cells. A name normally refers to its defined cells whether rows are hidden or filtered out. If the task is specifically to process visible cells only, that is a separate requirement: the code needs to identify visible cells, and the result can be a multi-area range. Test that behavior on the workbook’s filter and hidden-row setup rather than assuming the name excludes them.
Common errors and how to fix them
If VBA stops on the line that sets target, check the name and its scope first. A misspelled name, a name defined on a different worksheet, or a reference to a deleted range can prevent the reference from resolving. Open Formulas > Name Manager and confirm the exact name, scope, and Refers to address. Also check that the code is pointing at the intended workbook and sheet.
Another frequent issue is that a defined name refers to a formula or constant rather than a range. For example, a name might represent a text value or a calculation. Such a name does not necessarily have a range that can be assigned through RefersToRange. The loop requires a real cell reference. Check the name’s Refers to entry and use a different approach if it represents a value rather than cells.
If the code runs but prints nothing, confirm that the Immediate window is open in the Visual Basic Editor and that the target range contains the values you expect. A loop over a correctly resolved but empty range still runs; it simply prints blank values. To inspect the target itself, pause the macro and examine target.Address in the Locals window or Immediate window.
A type mismatch often points to an error value being converted or compared as if it were an ordinary value. Test IsError(cell.Value2) before converting it with CStr or doing numeric work. If the range mixes text, numbers, blanks, and errors, define what each type should do rather than relying on implicit conversion.
Performance, safety, and workbook compatibility
For a small or moderate named range, a cell-by-cell loop is easy to read and maintain. For a very large range, repeated reads and writes between VBA and the worksheet can be slow. A common optimization is to read a contiguous range into a VBA array, process the array in memory, then write results back in one operation. That technique needs extra care: a one-cell range can return a scalar rather than a two-dimensional array, and multi-area ranges do not behave like one simple rectangular block.
Keep the first version simple and confirm its results before optimizing. If a macro will change cells, work on a copy and consider limiting its target to the named range rather than using broad selections such as an entire worksheet column. Avoid relying on Select and Activate when direct references are available; direct references are less dependent on the user’s current selection.
On Excel for Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11, then use Insert > Module to add a standard module. Run a macro from the editor or through Excel’s macro controls. Save a macro-enabled workbook as .xlsm if it must retain VBA code. On Excel for Mac, VBA is supported, but the editor shortcut can differ: Option+F11 may require the Fn key depending on the keyboard. Excel for the web cannot create, edit, or run VBA macros; open the file in desktop Excel to use the macro.
Macro availability also depends on security settings and file origin. Only enable macros for workbooks you trust. A macro-enabled file is not automatically safe just because it is an Excel workbook, and a macro that changes cell values can have lasting effects. Keep an unchanged backup when testing unfamiliar code.
When a loop is not the best option
If the purpose is only to calculate a total, count entries, or find a maximum, a worksheet formula may be simpler than VBA. A formula updates as the data changes and does not require macros to be enabled. Similarly, Excel’s built-in filtering, sorting, and conditional formatting tools may be a better fit when the task is visual or interactive rather than an automated transformation.
Use VBA when the work involves repeated actions, branching rules, interaction with other workbook objects, or a process that is cumbersome to express with worksheet formulas. A named range can make that code easier to maintain because the code refers to a meaningful name rather than an address that may change. The important distinction is that the name identifies the cells; the loop determines what happens to each one.
Frequently Asked Questions
How do I loop through a named range in Excel VBA?
Assign the named range to a Range variable, then use For Each cell In target.Cells. Qualify the name with its worksheet or workbook when scope could be ambiguous.
Can I use For Each with a named range?
Yes. A range is a collection of cells that VBA can enumerate, so For Each is a direct way to process each cell. Declare the loop variable as Range.
How do I refer to a workbook-level named range?
Use ThisWorkbook.Names("NameHere").RefersToRange when the name belongs to the workbook containing the code. Replace NameHere with the actual name and ensure it refers to cells.
Does a named-range loop skip blank or hidden cells?
No. The loop visits the cells in the name’s reference, including blanks and cells in hidden or filtered rows. Add a condition to skip blanks, or use a visible-cells-specific approach if that is required.
Can I loop through a named range on another worksheet?
Yes. Reference the worksheet explicitly, for example with ThisWorkbook.Worksheets("Sheet1").Range("SalesInput"). This avoids depending on the active sheet and helps distinguish worksheet-scoped names.
Related
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Copy Range to Another Sheet: Methods and TipsTo copy a range to another worksheet with Excel VBA, qualify the source and destination sheets and use…
- VBA Function Arguments: Types, Defaults, and ExamplesVBA function arguments are the values or variables supplied when a function is called; the function’s parameter list…
- Excel VBA Module: How It Works, Types, and When to Use OneAn Excel VBA module is a container for Visual Basic for Applications code, such as macros, helper procedures,…
- VBA Pivot Table Delete: What to Remove and HowYes. In Excel for Microsoft 365 on Windows, remove a PivotTable report with VBA by clearing its full…
- Outlook Macros: How to Enable Them Safely in Classic OutlookTo enable VBA macros in classic Outlook for Windows, open File > Options > Trust Center > Trust…
- VBA Display UserForm: Methods, Examples, and FixesIn Excel VBA, display a UserForm by calling its Show method, such as frmEntry.Show. By default, the form…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.