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

We will use a dataset that contains information about the Sales Amount of the first two months of some sellers.

Example 1 – Apply VBA with the Range Variable to Loop through Rows and Columns in a Range in Excel

STEPS:

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.

Select Insert in the Visual Basic window.
Select Module. This will open the Module window.

Insert this 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

The 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, and 10, respectively.

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

Select the code and Run it from the Macro window.

Here are the results.

To loop through columns in a range, use 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.

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

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

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.

STEPS:

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.

Select Insert and then Module in the Visual Basic window.

Insert this 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.

Press Ctrl + S to save the code and close the Visual Basic window.
Go to the Developer tab and select Macros. It will open the Macro window.

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

You will see results below.

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

STEPS:

Open the Visual Basic window: go to the Developer tab and select Visual Basic or press Alt + F11.

Select Insert and then Module in the Visual Basic window.

Insert this 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.

Press Ctrl + S to save the code.
Close the Visual Basic window.
Select the range. We have selected Cell C6 to C10.

Go to the Developer tab and select Macros to open the Macro window.

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

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

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

This will repeat until the last cell of the range.

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

In this case, we will fill the dataset with a specific value. So, we need a structure like the one below.

STEPS:

Go to the Developer tab and select Visual Basic to open a Visual Basic window.

Select Insert and choose Module. This will open the Module window.

Insert this 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.

Press Ctrl + S to save the code.
Close the Visual Basic window.
Write the values in Cell B1 and B2. We have entered 6 from Cell B1 and C in Cell B2.

Press Alt + F8 to open the Macro window.
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).

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

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

Case 5.1 – Entire Row

STEPS:

Select Visual Basic from the Developer tab to open the Visual Basic window.

Select Insert and choose Module in the Visual Basic window.

Insert this 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

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.

Press Ctrl + S to save the code.
Go to the Developer tab and select Macros.

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

You will see results like the picture below.

Case 5.2 – Entire Column

STEPS:

Press Alt + F11 to open the Visual Basic window.
Select Insert and pick Module. It will open the Module window.
Insert this 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