Loop through a Range for Each Cell with Excel VBA (Ultimate Guide)

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.


Download Practice Workbook

Download this practice workbook


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?

In this section, we will show you step by step how it works in Excel with a simple example. First, take a look at this dataset:

dataset for each cell in range vba excel

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.

insert VBA module in excel

📌 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.

output of excel for each cell in range vba

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.

working range of the datatset

After that, from that loop, our cell variable will point to cell B5 first.

first pointed cell in Excel

Then, it will add 5 with the cell value and update it.

add 5 with the first value

After that, the cell variable will point to cell B6.

newly pointed cell

Similar to the previous steps, it will add five with the cell value and update it.

updated value in the dataset

All the cells will go through this process and the code will add five with each of them.

output of excel for each cell in range vba

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 Sub

It will multiply each cell of row 2 with the value 5.

It will look like the following screenshot:

rows multiplied by a value using vba 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 Sub

Basically, it will multiply each cell of column B with the value 5. After that, it will look like the following screenshot:

excel for each cell in range vba used on column


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 Sub

This 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:

select a range of cells in excel

Apply the macro. After that, you will see the output.

range of cells filled with values using for each loop excel


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 Sub

Here, we have some values in an array. We take them in for each loop and show them in a message box in Excel.


Similar Readings:


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 Sub

This 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 Sub

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 Sub

This 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 Sub

We will apply this code to the following dataset:

dataset for each cell in range vba in Excel

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:

output of Excel for each cell in range vba

As you can see, we have successfully used the If condition in the “for each cell in Range” loop and successfully gave the remarks.


💬 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.


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Tags:

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo