You can loop through a named range in Excel VBA very easily. 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. In this article, we will show how to use Excel VBA to Loop Through named range. Follow this article to be able to do that all by yourself.
Here, as an example, you can see we used the For loop with the Offset function in the VBA code to loop through a named range. There are other ways to do this. Follow the methods to learn to do it by yourself.
How to Launch VBA Macro Editor in Excel
VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. You can follow these simple steps to open the VBA editor too.
Steps:
- Firstly, we will open the Developer tab.
- Then, we will select Visual Basic.
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the 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. It is better than using regular cell references. You can learn how to create a named range in Excel from the below steps.
- First, select the range of cells that you want to name.
- Next, click on the Formulas tab from the top menu ribbon.
- After that, click on the Define Name group and select the Define Name… button from the dropdown menu.
- Then, the Name Manager window will open on the screen. Type the name you want to give for the range beside the Name.
- Make sure to check if the cell references in the Refers to box match your selection.
- Finally, 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
In this article, we will show 6 examples of looping through a Named Range in Excel VBA.
Here, we have some product codes with mixed case letters. We will have to make the product ID with all uppercase letters.
We can keep all the product ID codes in a named range and loop through the range to correct the codes in Excel VBA.
Example 1: Looping Through Named Range with 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 in it repeatedly for each element in the group. We are going to utilize that to loop through the named range here.
In the above image, you can see the VBA code to loop through a named range with a for each loop. Copy the following VBA codes and paste them 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 Sub🔎 VBA Breakdown
Sub ForEach()- First, we create a sub-procedure named ForEach().
Dim myRange As Range- Then, we declare a range object named “myRange”.
Set myRange = Range("ProductID")- After that, we assign the named range “ProductID” using Range.
Dim cell As Range- Next, 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 cellNow, the code will initiate a For Each loop and it will iterate through each cell in the named range, the “cell” variable is set 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 to Loop Through Named Range
We can use the For loop with index to loop through the named range. The For loop helps an operation iterate over a named range. The index variable is used to track the current position and access each cell in the range in sequence.
Here, we used the For loop with Index to loop through a named range in Excel VBA. Copy the following VBA codes and paste them into your 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 Sub🔎 VBA Breakdown
Sub ForLoopIndex()- The sub-procedure is ForLoopIndex() and ProductID is the named range.
Dim myRange As Range
Set myRange = Range("ProductID")- Then, the Dim statement declares the variable “i” as a long-type integer. It will be the index variable for a loop.
For i = 1 To myRange.Rows.Count- After that, the code sets 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)- Next, it accesses each cell in the second column of the current row, the Ucase function converts the text in the cell to uppercase and prints in the new cell.
Next i
- Finally, the loop increases the index variable “i” by 1 each time it iterates until the condition is no longer satisfied.
Example 3: Loop Through Named Range with 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. We will apply that here to loop through the named range here.
In this code, we used For loop with Offset to loop through a named range in Excel VBA. Copy the following VBA codes and paste them 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 Sub🔎 VBA 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: Looping Through Named Range with 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.
The above image shows the VBA code to loop through a named range using an array. Copy the following VBA codes and paste them 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 Sub🔎 VBA Breakdown
Dim arr() As Variant statement - First, the line creates an array called “arr” as a Variant and assigns values from the ‘ProductID’ named range.
For i = LBound(arr) To UBound(arr)
arr(i, 1) = UCase(arr(i, 1))- The ‘For loop’ repeats over each row in the array.
arr(i, 1) = UCase(arr(i, 1))- The Ucase function capitalizes the values in the first column.
Example 5: Utilizing UCase to Loop Through Named Range
The VBA UCase function is similar to the Excel UPPER function. Here we use the UCase function in Excel VBA to make any text uppercase. This function can loop through a named range to convert text.
We can utilize the UCase function to loop through a named range in Excel VBA like the above-mentioned code. Copy the following VBA codes and paste them 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 Sub🔎 VBA 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 iNext, a For loop iterates over the range and gives output in the next column using the Ucase function.
Example 6: Loop Through Named Range Using Do Until Loop
The Do Until loop allows to perform some action on each cell in the range until the end of the range. We can use this to loop through a named range. It will iterate the operation until the end of the named range.
The above image contains a VBA code to loop through a named range using the Do Until loop in Excel VBA. Copy the following VBA codes and paste them 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 Sub🔎 VBA Breakdown
Dim i As Long- It declares a long integer-type variable named “i”. Then initialize it to 1.
Do Until myRange.Cells(i, 1) = ""- Then a Do Until loop iterates through the rows in 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.
Read More: How to Set Value to a Named Range with Excel VBA
An Alternate Way to Apply Changes to Named Range in Excel VBA
Besides all the looping described above, we can also apply formulas directly for a range or named range. For this example, we are going to achieve the same result using the UPPER function.
The UPPER function is very popular in Excel. It helps to convert any text string to uppercase. Here, we will use this function to loop through a named range in Excel VBA.
In the above image, we applied the UPPER function in Excel VBA to loop through a named range. Copy the following VBA codes and paste them 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 Sub🔎 VBA Breakdown
Dim formula As String- Here, the Dim statement takes 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. This property 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 one column next to the first column.
How to Loop Through Rows in Range Using Excel VBA
We can also loop through rows in range using Excel VBA. We can insert any specific range in the VBA code and run a for loop to act. In the below code, we will show how to do that.
You can also loop through rows in a specific range using Excel VBA. Copy the following VBA codes and paste them 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")- 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 a named range. 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. It helps to avoid errors and ensure that the code runs smoothly.
Download Practice Workbook
You can download the practice Excel workbook from the download button below.
Conclusion
In this article, we showed several examples of Excel VBA to loop through a named range. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
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
