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

In this tutorial, we will cover how to use VBA to loop through rows in range in excel. We utilize loops to prevent doing the same thing over and over again. While working in Microsoft Excel, we may find ourselves in a position where we must perform the same task numerous times. We can do this easily using loops in VBA. In this article, we will demonstrate 6 examples to loop through rows in excel with VBA. The loop that we will use throughout the examples is the ‘For-Next Loop’.


Download Practice Workbook

We can download the practice workbook from here.


6 Examples to Loop Through Rows in Range with VBA in Excel

To illustrate the examples of this tutorial, we will use the following dataset. The dataset contains sales amounts for the first two months of 5 Salespeople.

6 Examples to Loop Through Rows in Range with VBA in Excel


1. Use VBA in Range with Range Variable to Loop Through Rows

In the first example, we will use the range variable to loop through rows in range using VBA in excel. We will apply a VBA loop in the following dataset.

Use VBA in Range with Range Variable to Loop Through Rows

Let’s see the steps to perform this example.

STEPS:

  • To begin with, go to the active worksheet ‘Range Variable’.
  • In addition, right-click and select the option ‘View Code’. You can also press Alt + F11 to open it.

Use VBA in Range with Range Variable to Loop Through Rows

  • The above action opens a code window for that worksheet.
  • Furthermore, type the code in the code window:
Sub VBA_Loop_through_Rows()
Dim w As Range
For Each w In Range("B5:D9").Rows
w.Cells(1).Interior.ColorIndex = 35
Next
End Sub
  • Then, click on the Run or press F5 to run the code.

Use VBA in Range with Range Variable to Loop Through Rows

  • Finally, we will get results like the following screenshot.

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


2. Apply VBA to Loop Through Rows in Range with Numeric Variable

Another option for looping through rows in a range is to select the variable. In the second example, we will apply VBA in the following dataset to loop through rows in range with numeric variables.

Apply VBA to Loop Through Rows in Range with Numeric Variable

Let’s take a look at the steps to do this method.

STEPS:

  • First, right-click on the active sheet named ‘Numeric Value’.
  • Next, select the option ‘View Code’.

Apply VBA to Loop Through Rows in Range with Numeric Variable

  • This action will open a code window for that worksheet. You can also press Alt + F11 to open that code window.
  • type the following code in that window:
Sub VBA_Numeric_Variable()
Dim w As Integer
With Range("B5").CurrentRegion
For w = 1 To .Columns.Count
.Columns(w).NumberFormat = "$0.00"
Next
End With
End Sub
  • After that, click on Run or press the F5 key to run the code.

  • Finally, we can see results like the following picture. The above code changes the format of the number into decimal points.

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


3. Excel VBA in User-Selected Range to Loop Through Rows in Range

In the third example, we will use VBA in a user-selected range to loop through rows in a range. So, the user will be able to apply a loop in a selected area of the dataset.

Excel VBA in User-Selected Range to Loop Through Rows in Range

Let’s see the steps associated with this example.

STEPS:

  • In the beginning, select the cell range (D5:D9).

Excel VBA in User-Selected Range to Loop Through Rows in Range

  • Next, right-click on the active sheet named ‘User Selected’. Select the option ‘View Code’.

Excel VBA in User-Selected Range to Loop Through Rows in Range

  • The above command will open a VBA code window for the active worksheet. You can also open that code window by pressing Alt + F11. Insert the following code in that blank code window:
Sub VBA_User_Selection()
Dim w As Variant
Set xRange = Selection
For Each w In xRange
MsgBox "Cell value = " & w.Value
Next w
End Sub
  • Then, to run the code for that worksheet click on the Run or press the F5 key.

Excel VBA in User-Selected Range to Loop Through Rows in Range

  • So, a message box appears showing the first value of the selected range.

  • Moreover, if you click on OK it will return the second value of the selected range which is cell D6.

  • This process will continue until the last value of the selected range which is cell D9.

Read More: How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)


Similar Readings


4. Loop Through Rows in Dynamic Range with VBA

In the fourth example, we will apply VBA to loop through rows in dynamic range. This example is slightly different from the previous ones. We will be able to customize the range in the loop for the excel worksheet. We will use the following dataset to illustrate this method. In the following dataset, we will fill the values of the range (B8:C12) with a specific value.

Loop Through Rows in Dynamic Range with VBA

Just go through the below steps to perform this method.

STEPS:

  • Firstly, input value 6 in cell B1 and C in cell B2.
  • Secondly, right-click on the active cell and select the option ‘View Code’.

Loop Through Rows in Dynamic Range with VBA

  • This command will open a VBA code window for the active worksheet. Another way to open that code window is by pressing Alt + F11.
  • Thirdly insert the following code in that code window:
Sub 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 = "$2500.00"
Next Cell
Next Row
End Sub
  • Now, to run the code click on the Run or press the F5 key.

Loop Through Rows in Dynamic Range with VBA

  • As a result, the dataset fills with the value $2500.00 in the following way.

NOTE:

In the above image, the value 6 represents the first two rows of the range (B8:B9).

  • Lastly, input the value 9 in cell B1 instead of 6. We can see the results in the following image.


5. Insert VBA to Loop Through Entire Row in Range

In the fifth example, we will see how we can apply VBA to loop through the entire row in range. This example will find the position of a specific value from selected one or multiple rows.

Insert VBA to Loop Through Entire Row in Range

So, let’s see the steps that we will follow to execute this example.

STEPS:

  • To begin with, right-click on the active sheet named ‘Entire Row’. Select the option ‘View Code’.

Insert VBA to Loop Through Entire Row in Range

  • The above command opens a blank VBA code window for the active worksheet. We can also get this code window by pressing Alt + F11.
  • Next, insert the following code in that code window:
Sub VBA_Loop_Entire_Row()
Dim w As Range
For Each w In Range("5:9")
If w.Value = "Chris" Then
MsgBox "Chris found at " & w.Address
End If
Next w
End Sub
  • Then, click on the Run or press the F5 key to run the code.

Insert VBA to Loop Through Entire Row in Range

  • In the above image, the highlighted value ‘Chris’ indicates the value that we will search for. Range value ‘5:9’ indicates we will search the value in the cell range (B5:B9).
  • Finally, a message box displays that the value ‘Chris’ is located in cell B6.

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


6. Loop Through Every n-th Row in Range with Excel VBA

In the last example, we will apply VBA to loop through every n-th Row in range. In the following dataset, we will apply color shading to the odd number rows of our data range.

Loop Through Every n-th Row in Range with Excel VBA

So, let’s see the steps to perform this method.

STEPS:

  • First, right-click on the active sheet named ‘n-th Row’. Select the option ‘View Code’.

Loop Through Every n-th Row in Range with Excel VBA

  • Next, it opens a blank VBA code window for that worksheet. We can also get this code window by pressing Alt + F11.
  • Then, type the following code in that code window next:
Sub ShadeRows1()
Dim r As Long
With Range("B5").CurrentRegion
For r = 1 To .Rows.Count
If r / 2 = Int(r / 2) Then 'even rows
.Rows(r).Interior.ColorIndex = 43
End If
Next
End With
End Sub
  • Now, click on the Run or press the F5 key to run the code.

  • Lastly, we can see that the above code shades only the odd number rows from our dataset.

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


Conclusion

In conclusion, this tutorial gives you 6 examples of looping through rows in range with VBA in excel. Download the practice worksheet contained in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to respond to your message as soon as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo