VBA code in Microsoft Excel is one of those tools that can help a user perform complex operations at ease. If you are working with that, you know how much time it has saved of yours. One of the most common parts of the VBA is Loop. You can perform that in a lot of ways. But in this tutorial, we will discuss For each loop. Especially, for each cell in a range using VBA in Excel.
What Does Range Mean in VBA?
Before we start, we should discuss the Range of VBA codes. In this section, we will discuss the Range in the Loop. And also what can the ranges be in our for each Loop in Excel.
Basically, the range is a cell or collection of cells in simple terms. In VBA codes of Excel, we consider group cells as a Range. We can perform a lot of operations by selecting a Range.
Now, For each Loop, we use a range to loop through that. We can select not only cells but also a lot of other things as a Range.
We can select the following as Range:
For each cell in Range(“B1:B10”):
It will loop through the cells between B1 to B10.
For each ws in Sheets:
It will loop through the sheets in the workbook.
For each wb in Workbooks:
It will loop through Excel’s workbook.
For Each s In Sheets(“Sheet2”).Shapes:
It will loop through the shapes inserted in the worksheet.
For Each pivot In Sheets(“Sheet6”).PivotTables:
It will go through the pivot tables in the selected sheet.
For Each table In Sheets(“Sheet7”).ListObjects:
It will go through the tables in a sheet.
Loop through a Range for Each Cell: How Does It Work in Excel?
This section will show step-by-step how to apply a loop for each cell in a range using Excel VBA with a simple example. First, take a look at this dataset:
Here, we have some values in a column. Our goal is to loop through all of them and add five with each of the values. Let’s dive into it.
📌 Step 1: Open the VBA Editor in Excel
Press Alt+F11 on your keyboard. then select Insert > Module.
📌 Step 2: Build the Code
Now, type the following code in the editor:
Sub add_five()
Dim cell As Range
For Each cell In Range("B5:B9")
cell.Value = cell.Value + 5
Next cell
End Sub📌 Step 3: Run the Code
Press Alt+F8 on your keyboard. Select add_five. Then, click on Run. After that, you will see this output.
As you can see, we have successfully added five with each of those numbers.
🔎 Breakdown of the Code
For each cell in Range(“B5:B9”): Here, our working range was B5 to B9.
After that, from that loop, our cell variable will point to cell B5 first.
Then, it will add 5 with the cell value and update it.
After that, the cell variable will point to cell B6.
Similar to the previous steps, it will add five with the cell value and update it.
All the cells will go through this process and the code will add five to each of them.
As you can see, we have updated all the values using the “for each cell in range” loop successfully.
Loop through a Range for Each Cell: Some Useful Examples
In this section, we will provide you with some VBA code snippet that you can implement in your workbook. Make sure you check all of these. I hope it will give you a clear idea about the “for each cell in Range” loop of VBA surely.
1. Loop through Entire Row
Now, if you don’t want to select a particular range but the entire row, you can use the following code:
Sub entire_row()
Dim cell As Range
For Each cell In Range("2:2")
cell.Value = cell.Value * 5
Next cell
End SubIt will multiply each cell of row 2 with the value 5.
It will look like the following screenshot:
Read More: How to Use VBA for Each Row in a Range in Excel
2. Loop through Entire Column
Similarly. If you want to loop through an entire column and perform something, you can apply the following code:
Sub entire_column()
Dim cell As Range
For Each cell In Range("B:B")
cell.Value = cell.Value * 5
Next cell
End SubBasically, it will multiply each cell of column B with the value 5. After that, it will look like the following screenshot:
3. Loop through User-selected Range in Excel
It is easier to select cells with your mouse. Sometimes, to perform some operations, we select a range of cells according to our choice. If you don’t want to specify the range of cells but choose with your mouse, you can follow this code:
Sub user_selected()
Dim cell As Range
Set range_of_cells = Selection
For Each cell In range_of_cells
cell.Value = "Hello"
Next cell
End SubThis code basically sets the cell’s value to “Hello”. Notice here, we didn’t specify any range of cells. Instead, we used Selection. It will perform this operation on a user-selected range. You can understand it by the following screenshot.
Select a range of cells:
Apply the macro. After that, you will see the output.
4. Loop through Array Using for Each Range
Now, if you have an array consisting of multiple items in it, you can use this code to apply any kind of task.
Sub loop_array()
Dim array_value As Variant
Dim val As Variant
array_value = Array("Apple", "Orange", "Banana")
For Each val In array_value
MsgBox val
Next val
End SubHere, we have some values in an array. We take them in for each loop and show them in a message box in Excel.
5. Sheets as Range in VBA
You can also select sheets as a range in VBA. Basically, we can perform operations across multiple sheets. That’s why we are selecting sheets as a range.
Sub loop_sheets()
Dim sheet As Worksheet
For Each sheet In Sheets
sheet.Visible = True
Next sheet
End SubThis code snippet will unhide all the sheets in a workbook.
6. Using Workbooks for Each Loop
Also, we can use workbooks as a range in VBA Excel. The following code snippet will take workbooks as a range and close all the workbook at once.
Sub loop_workbooks()
Dim wBook As Workbook
For Each wBook In Workbooks
wBook.Close
Next wBook
End SubRead More: How to Use For Each Loop in Excel VBA
7. Loop through Table in Excel
We can select tables as a range in our VBA codes.
Sub loop_tables()
Dim table As ListObject
For Each table In Sheets("Table").ListObjects
table.Delete
Next table
End SubThis VBA code will take the table as a range from the sheet “Table” and delete it.
8. Using Conditions for Each Cell in Range with VBA
If you have an idea about If condition in Excel, you mix it up with the for each loop. This is one of the important tasks in VBA. The following sample will give a clear idea about that:
Sub loop_if()
Dim cell As Range
For Each cell In Range("C5:C9")
If cell.Value < 33 Then
cell.Offset(o, 1).Value = "Failed"
Else
cell.Offset(0, 1).Value = "Passed"
End If
Next cell
End SubWe will apply this code to the following dataset:
Here, we have a dataset of some students’ marks. Our goal is to analyze their marks, and give a remark whether it is Passed or Failed. If someone scores more than 32, he/she got passing marks. Otherwise, it will consider the marks as Failed.
If we run the code, we will get the following result:
As you can see, we have successfully used the If condition in the “for each cell in Range” loop and successfully gave the remarks.
Read More: How to Use VBA for Each Cell in Range in Excel
💬 Things to Remember
✎ If you perform any operations throughout the whole columns or rows, it will change the whole columns/rows output.
✎ Your loop variable should be the as same type as the Range type you selected. If your range type is Worksheets, your loop variable should be Worksheet type.
Download Practice Workbook
Download this practice workbook
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about “for each cell in Range” of VBA in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!
