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.


Download Practice Book

Download the practice book here.


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

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.

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

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

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

  • 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

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

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.

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

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

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

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

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

  • 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

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

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.

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

Read More: VBA to Loop Through Rows in Range in Excel (6 Examples)


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.

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

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.

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

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

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

  • 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

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

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.

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

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

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

  • Finally, you will see results like the below picture.

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

Read More: Excel VBA to Loop through Range until Empty Cell (4 Examples)


Similar Readings


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.

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

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

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

  • 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

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

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.

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

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

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

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

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

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

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

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

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

  • It will repeat till the last cell of the range.

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

Read More: How to Use VBA for Each Row in a Range in Excel


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.

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

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.

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

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

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

  • 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

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

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.

Read More: How to Convert Range to Array in Excel VBA (3 Ways)


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.

Read More: Excel VBA: Loop Through Columns in Range (5 Examples)


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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo