VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)

In this article, we will learn to loop through rows and columns in a range in Excel with VBA. Sometimes, we need to repeat a task many times in an Excel sheet. This becomes easy if we use loops in VBA. Today, we will try to loop through rows and columns in a range with VBA for various purposes. Here, we will demonstrate 5 examples. In these examples, we will mainly use the For-Next Loop.

Loop through Rows and Columns in a Range in Excel: 5 VBA Examples

To explain the examples, we will use a dataset that contains information about the Sales Amount of the first two months of some sellers.

1. Apply VBA with Range Variable to Loop through Rows and Columns in a Range in Excel

In the first example, we will loop through rows and columns in a range with a range variable using VBA in Excel. To learn more, follow the steps below.

STEPS:

First of all, go to the Developer tab and select Visual Basic. It will open the Visual Basic window. You can also press Alt + F11 to open it.

Secondly, select Insert in the Visual Basic window. A drop-down menu will occur.
Then, select Module. This will open the Module window.

Thirdly, type the code in the Module window:

Sub Loop_through_Rows()
Dim z As Range
For Each z In Range("B6:D10").Rows
    z.Cells(2).Interior.ColorIndex = 35
Next
End Sub

Here, this code will fill the second cell of each row in the range (B6:D10) with color. First, it will work on Row 6. After that, it will work on Rows 7, 8, 9 & 10 respectively.

Press Ctrl + S to save the code and then, close the Visual Basic window.
Next, select Macros from the Developer tab. The Macro window will appear.

After that, select the code and Run it from the Macro window.

After running the code, you will see results like the below picture.

Again, to loop through columns in a range, type the below code in the Module window:

Sub Loop_through_Columns()
Dim z As Range
For Each z In Range("B6:D10").Columns
    z.Cells(2).Interior.ColorIndex = 35
Next
End Sub

Here, we have used .Columns instead of .Rows. This time, the loop will work on Column B first and then, fill Cell B7 with color. After that, it will work on Columns C and D respectively.

Finally, press Ctrl + S to save the code, and then, press the F5 key to run the code. It will show results like the below screenshot.

2. Excel VBA with Numeric Variable to Loop through Rows and Columns in a Range

There is another way to set the variable for looping through rows and columns in range. In this case, we will use the numeric variable. Here, we will use the same dataset. But, the number format will be without the decimal points. We will use the numeric variable to change the number format to decimal points using a loop.

Let’s follow the steps below to learn more.

STEPS:

In the first place, go to the Developer tab and select Visual Basic. This will open the Visual Basic window. You can also press Alt + F11 to open it.

After that, select Insert and then Module in the Visual Basic window.

Next, type the code in the Module window:

Sub Numeric_Variable()
Dim z As Integer
With Range("B6").CurrentRegion
For z = 1 To .Columns.Count
    .Columns(z).NumberFormat = "$0.00"
Next
End With
End Sub

Here, this code will work on Cell B6 to the current region. In this case, the current region is Cell B6 to D10. It will check the numeric values in each column and then, apply the number format.

Now, press Ctrl + S to save the code and then, close the Visual Basic window.
Then, go to the Developer tab again and select Macros. It will open the Macro window.

Select the desired code from the Macro window and Run it.

Finally, you will see results like the below picture.

3. Loop through Rows and Columns in a User-Selected Range with Excel VBA

In the third example, we will apply the VBA code to loop through in a user-selected range. Here, the user will select a range first and then, run the code. The code will display the cell value in a message box. Again, we will use the same dataset.

Let’s pay attention to the steps below to learn more.

STEPS:

Firstly, we need to open the Visual Basic window. To do so, go to the Developer tab and select Visual Basic. You can also press Alt + F11 to open it.

Secondly, select Insert and then Module in the Visual Basic window.

Thirdly, type the code in the Module window:

Sub User_Selection()
Dim z As Variant
Set xRange = Selection
For Each z In xRange
    MsgBox "Cell value = " & z.Value
Next z
End Sub

In the previous codes, we declared the range in the code. But, here, we have set the range as Selection. This code will display the cell value of the selected range one by one in the message box.

Now, press Ctrl + S to save the code.
Then, close the Visual Basic window.
After that, select the range. Here, we have selected Cell C6 to C10.

Next, go to the Developer tab again and select Macros to open the Macro window.

Select the desired code and Run it from the Macro window.

Finally, it will display the cell value in the message box like below. Here, the message box is displaying the value of Cell C6.

Again, if you click OK, it will display the value of Cell C7.

It will repeat till the last cell of the range.

4. Insert VBA to Loop through Rows and Columns in a Dynamic Range in Excel

In this example, we will use the loop through rows and columns in a dynamic range. The code will enter a specific value in the cells of the range. Here, we will be able to update the range from the Excel sheet. In this case, we will fill the dataset with a specific value. So, we need a structure like the one below.

Let’s observe the steps below to learn more.

STEPS:

To begin with, go to the Developer tab and select Visual Basic to open a Visual Basic window.

In the following, select Insert and then, Module. This will open the Module window.

After that, type the code in the Module window:

Sub Loop_through_Dynamic_Range()
Dim xRange As String
xRange = "B8:" + Worksheets("Dynamic Range").Cells(2, 2).Value + _
CStr(3 + Worksheets("Dynamic Range").Cells(1, 2).Value)
For Each Row In Range(xRange)
    For Each Cell In Row
        Cell.Value = "$3200.00"
    Next Cell
Next Row
End Sub

Here, we have used xRange as the variable. The range starts from Cell B8 in our example. This code will enter $3200.00 in each cell of the range.

Now, press Ctrl + S to save the code.
Then, close the Visual Basic window.
After that, write the values in Cell B1 and B2. Here, we have entered 6 from Cell B1 and C in Cell B2.

Again, press Alt + F8 to open the Macro window.
Then, Run the desired code.

You will see results like the picture below.

Here, 6 is representing the first two rows of the range(B8:C9).

Finally, if you change the value of Cell B1 to 8, you will see results like the below.

5. Loop through Entire Rows and Columns in a Range in Excel with VBA

In the last example, we will loop through the entire row and column with VBA. We will first loop through the entire row and then, the entire column. We will use the same dataset again.

5.1 Entire Row

In this sub-method, we will show how we can loop through an entire row. Follow the steps below to learn more.

STEPS:

Firstly, select Visual Basic from the Developer tab to open the Visual Basic window.

Secondly, select Insert and then Module in the Visual Basic window.

After that, type the VBA code in the Module window:

Sub Loop_Entire_Row()
Dim z As Range
For Each z In Range("7:7")
If z.Value = "Sophie" Then
    MsgBox "Sophie found at " & z.Address
End If
Next z
End Sub

Here, this code will loop through Row 7 and find the word ‘Sophie’. If it finds the word in a cell of the row, then, it will display the cell address in the message box.

Next, press Ctrl + S to save the code.
Now, go to the Developer tab and select Macros.

After that, select the desired code from the Macro window and Run it.

Finally, you will see results like the picture below.

5.2 Entire Column

Let’s follow the steps below to loop through an entire column.

STEPS:

In the first place, press Alt + F11 to open the Visual Basic window.
Select Insert and then, Module. It will open the Module window.
Secondly, type the code in the Module window:

Sub Loop_Entire_Column()
Dim z As Range
For Each z In Range("B:B")
If z.Value = "Sophie" Then
    MsgBox "Sophie found at " & z.Address
End If
Next z
End Sub

Here, we have used Range(“B:B”) instead of Range(“1:1”). This code will search for the word ‘Sophie’ in Column B only.

Now, press Ctrl + S to save the code.
Then, press the F5 key to open the Macro window.
Next, select the desired code and Run it.

In conclusion, you will see results like the picture below.

Download Practice Book

Download the practice book here.

Loop through Rows & Columns in a Range.xlsm

Conclusion

We have demonstrated 5 easy and important examples of Loop through Rows and Columns in a Range in Excel with VBA. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

Excel VBA to Loop through Range until Empty Cell

Get FREE Advanced Excel Exercises with Solutions!

VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)

Loop through Rows and Columns in a Range in Excel: 5 VBA Examples

1. Apply VBA with Range Variable to Loop through Rows and Columns in a Range in Excel

2. Excel VBA with Numeric Variable to Loop through Rows and Columns in a Range

3. Loop through Rows and Columns in a User-Selected Range with Excel VBA

4. Insert VBA to Loop through Rows and Columns in a Dynamic Range in Excel

5. Loop through Entire Rows and Columns in a Range in Excel with VBA

5.1 Entire Row

5.2 Entire Column

Conclusion

Related Article

Leave a reply Cancel reply