Looping through a named range is just like looping through a regular range. The advantage is you can run a loop without having to explicitly reference each cell by its row and column coordinates. We can loop through a named range in Excel VBA very easily.
Here, as one example, we used the For loop with the Offset function in the VBA code to loop through a named range. There are several ways to do this, which we’ll demonstrate below.
How to Launch VBA Macro Editor in Excel
Press the keyboard shortcut Alt + F11 to launch the VBA editor. Alternatively, follow these simple steps:
Steps:
- Open the Developer tab.
- Select Visual Basic.
The Visual Basic window will open.
- From the Insert option, choose a new Module to write the VBA code.
How to Create a Named Range in Excel
Creating a Named Range in Excel is an effective way to refer to a specific range of cells with an identifying name, with many advantages over using regular cell references.
Steps:
- Select the range of cells to name.
- Click on the Formulas tab from the top menu ribbon.
- Click on the Define Name group and select the Define Name… button from the dropdown menu.
The Name Manager window will open.
- Enter the Name for the range.
- Make sure the cell references in the Refers to box match your selection.
- Click on the OK button to save the Named Range.
Read More: Excel VBA to Create Named Range from Selection
Excel VBA to Loop Through Named Range: 6 Suitable Examples
Here, we have some product codes with mixed case letters. We will reformat the product IDs to have all uppercase letters by using Excel VBA to store all the product ID codes in a named range, and then loop through the range to correct the IDs.
Example 1 – Using a For Each Loop
The For Each loop is a useful tool to iterate through elements in a collection like an array, a range of cells, etc. The loop executes the code within it repeatedly for each element in the group.
The above image shows the VBA code to loop through a named range with a For Each loop.
Copy the following VBA code and paste it into your Module:
Sub ForEach()
Dim myRange As Range
Set myRange = Range("ProductID")
Dim cell As Range
For Each cell In myRange.Cells
cell.Offset(0, 1). Value = UCase(cell.Value)
Next cell
End SubVBA Breakdown
Sub ForEach()- First, we create a sub-procedure named ForEach().
Dim myRange As Range- We declare a range object named “myRange”.
Set myRange = Range("ProductID")- We assign the named range “ProductID” using Range.
Dim cell As Range- The Dim statement declares a range object named “cell” to represent each cell in the named range.
For Each cell In myRange.Cells
cell.Offset(0, 1). Value = UCase(cell.Value)
Next cellWe initiate a For Each loop which iterates through each cell in the named range, setting the “cell” variable to the next cell in the range.
The Offset function with this particular set of arguments moves the current cell by one column to the right.
The UCase function changes the cell value to uppercase, and the next cell ends the current For Each loop and moves on to the next cell in the range.
- Finally, the sub-procedure ends with End Sub.
Example 2 – Using For Loop with Index
The For loop enables an operation to iterate over a named range. The Index variable is used to track the current position and access each cell in the range in sequence.
Copy the following VBA code and paste it into the Module:
Sub ForLoopIndex()
Dim myRange As Range
Set myRange = Range("ProductID")
Dim i As Long
For i = 1 To myRange.Rows.Count
myRange.Cells(i, 2).Value = UCase(myRange.Cells(i, 1).Value)
Next i
End SubVBA Breakdown
Sub ForLoopIndex()- The sub-procedure is ForLoopIndex() and ProductID is the named range.
Dim myRange As Range
Set myRange = Range("ProductID")- The Dim statement declares the variable “i” as a long-type integer, which will be the index variable for a loop.
For i = 1 To myRange.Rows.Count- We set the For loop to iterate from 1 to the number of rows in the “myRange” object at the beginning of the loop.
Cells(i, 2).Value = UCase(myRange.Cells(i, 1).Value)- In each cell in the second column of the current row, the Ucase function converts the text to uppercase and prints in the new cell.
Next i
- The loop increases the index variable “i” by 1 each time it iterates until the condition is no longer satisfied.
Example 3 – Using a For Loop with Offset
The Range.Offset is a very useful property that allows moving data to a specific cell from the current cell. We can use the For loop with the Offset function to get output in another cell for a particular range.
Copy the following VBA code and paste it into your Module:
Sub ForLoopOffset()
Dim myRange As Range
Set myRange = Range("ProductID")
Dim i As Long
For i = 1 To myRange.Rows.Count
myRange.Cells(i, 1).Offset(0, 1).Value = UCase(myRange.Cells(i, 1).Value)
Next i
End SubVBA Breakdown
For i = 1 To myRange.Rows.Count
myRange.Cells(i, 1).Offset(0, 1).Value = UCase(myRange.Cells(i, 1).Value)In the For loop, the Offset method changes the value of the cell one column to the right of the present cell. Then the Ucase function makes the cell value uppercase.
Example 4 – Using an Array
An array is a set or collection of values that can be accessed and altered using index values. Here we will loop through a named range from an upper bound to a lower bound of an array to perform a specific operation in each cell of the array.
Copy the following VBA code and paste it into your Module:
Sub ArrayRange()
Dim myRange As Range
Set myRange = Range("ProductID")
Dim arr() As Variant
arr = myRange.Value
Dim i As Long
For i = LBound(arr) To UBound(arr)
arr(i, 1) = UCase(arr(i, 1))
Next i
myRange.Offset(0, 1).Value = arr
End SubVBA Breakdown
Dim arr() As Variant statement - We create an array called “arr” as a Variant and assign values from the ‘ProductID’ named range.
For i = LBound(arr) To UBound(arr)
arr(i, 1) = UCase(arr(i, 1))- The ‘For loop’ iterates through each row in the array.
arr(i, 1) = UCase(arr(i, 1))- The Ucase function capitalizes the values in the first column.
Example 5 – Using UCase
The VBA UCase function is similar to the Excel UPPER function. This function can loop through a named range to convert text to uppercase.
Copy the following VBA code and paste it into your Module:
Sub WithEnd()
Dim myRange As Range
Set myRange = Range("ProductID")
With myRange
For i = 1 To .Rows.Count
.Cells(i, 2).Value = UCase(.Cells(i, 1).Value)
Next i
End With
End SubVBA Breakdown
- The With myRange starts a “With” statement that enables later code to reference the “myRange” object without repeatedly typing it in.
For i = 1 To .Rows.Count
.Cells(i, 2).Value = UCase(.Cells(i, 1).Value)
Next i- Then a For loop iterates over the range and returns output in the next column using the Ucase function.
Example 6 – Using a Do Until Loop
The Do Until loop allows performing some action on each cell in the range until the end of the range.
Copy the following VBA code and paste it into your Module:
Sub DoUntil()
Dim myRange As Range
Set myRange = Range("ProductID")
Dim i As Long
i = 1
Do Until myRange.Cells(i, 1) = ""
myRange.Cells(i, 2).Value = UCase(myRange.Cells(i, 1).Value)
i = i + 1
Loop
End SubVBA Breakdown
Dim i As Long- We declare a long integer-type variable named “i”, and initialize it to 1.
Do Until myRange.Cells(i, 1) = ""- Then a Do Until loop iterates through the rows in the range. The loop repeats until it reaches an empty cell in the first column of the range.
Cells(i, 2).Value = UCase(myRange.Cells(i, 1).Value)- The values in the first column change to uppercase and are assigned to the second column.
i = i + 1- The loop counter variable “i” is increased by 1 after each iteration.
An Alternate Way to Apply Changes to Named Range in Excel VBA
Besides all the looping methods described above, we can also apply formulas directly to a range or named range. For example, we can achieve the same result as the methods above using the UPPER function, which converts any text string to uppercase, directly on the range.
Copy the following VBA code and paste it into your Module:
Sub UpperFunction()
Dim myRange As Range
Set myRange = Range("ProductID")
Dim formula As String
formula = "=UPPER(" & myRange.Address & ")"
myRange.Offset(0, 1).FormulaArray = formula
myRange.Offset(0, 1).Value = myRange.Offset(0, 1).Value
End SubVBA Breakdown
Dim formula As String- The Dim statement specifies the formula as a string.
formula = "=UPPER(" & myRange.Address & ")"- The formula converts the values in the ‘ProductID’ named range using the Upper function.
- The “Address” property of the “myRange” object references the cell range in the formula.
Offset(0, 1).FormulaArray = formula- Then the code assigns the formula string to the cells in the next column of “myRange” using the “FormulaArray” property, which is used for array formulas that cover several cells.
Offset(0, 1).Value = myRange.Offset(0, 1).Value- The Offset function prints the value in the column next to the first column.
How to Loop Through Rows in Range Using Excel VBA
We can also loop through rows in a range using Excel VBA, by inserting any specific range in the code and running a For loop to act.
Copy the following VBA code and paste it into your Module:
Sub Rows()
Dim cell As Range
For Each cell In Range("C4:G4")
'loop through each cell in the range
cell.Offset(1, 0).Value = UCase(cell.Value)
Next cell
End SubVBA Breakdown
For Each cell In Range("C4:G4")- The For Each loop iterates through the given range “C4:G4”.
Offset(1, 0).Value = UCase(cell.Value)- The Offset function shifts the value one row below the current row and makes it uppercase using the Ucase function.
Things to Remember
- Make sure to create a named range before attempting to loop through one! Otherwise, the VBA code will not work.
- It is important to check the data type of the cell values in the named range before using them in your VBA code. This helps to avoid errors and ensure that the code runs smoothly.
Download Practice Workbook
Related Articles
- Create Dynamic Named Range with VBA in Excel
- Excel VBA to Delete Named Range
- How to Check If Named Range Exists with Excel VBA
