Method 1 – Using Excel VBA Macro with Range Variable to Loop Through Rows
STEPS:
- Go to the active worksheet ‘Range Variable’.
- Right-click and select the option ‘View Code’. You can also press Alt + F11 to open it.
- A code window for that worksheet will open.
- Enter 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- Click on Run or press F5 to run the code.
We will get results like the following screenshot.
Read More: Excel VBA: Loop Through Columns in Range
Method 2 – Applying VBA to Loop Through Rows with Numeric Variable
STEPS:
- Right-click on the active sheet named ‘Numeric Value’.
- Select the option ‘View Code’.
- A code window for that worksheet will open. Press Alt + F11 to open that code window.
- Enter the following code in the 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- Click Run or press the F5 key to run the code.
We can see the results in 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
Method 3 – Applying Macro with User-Selected Range to Loop Through Rows
STEPS:
- Select the cell range (D5:D9).
- Right-click on the active sheet named ‘User Selected’. Select the option ‘View Code’.
- A VBA code window for the active worksheet will open. You can also open that code window by pressing Alt + F11.
- Enter the following code in the 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- To run the code for that worksheet, click Run or press the F5 key.
- A message box appears showing the first value of the selected range.
- Click on OK to return the second value of the selected range, cell D6.
- Continue this process until the selected range’s last value, cell D9.
Read More: How to Use VBA to Count Rows in Range with Data in Excel
Method 4 – Performing VBA Loop Through Rows in Dynamic Range
STEPS:
- Input value 6 in cell B1 and C in cell B2.
- Right-click on the active cell and select the option ‘View Code’.
- A VBA code window for the active worksheet will open. Or press Alt + F11.
- Enter 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- Click Run or press the F5 key to run the code.
- The dataset fills with the value of $2500.00.
NOTE: In the above image, the value 6 represents the first two rows of the range (B8:B9).
- Input the value 9 in cell B1 instead of 6.
The results are shown in the following image.
Method 5 – Inserting a Loop Through an Entire Row in Excel Range
STEPS:
- Right-click on the active sheet named ‘Entire Row’. Select the option ‘View Code’.
- A blank VBA code window for the active worksheet will open. Or press Alt + F11.
- Enter 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- Click Run or press the F5 key to run the code.
- In the above image, the highlighted value ‘Chris’ indicates the value we will search for. The range value ‘5:9’ indicates we will search for the value in the cell range (B5:B9).
- A message box displays the value ‘Chris’ in cell B6.
Read More: How to Use VBA for Each Row in a Range in Excel
Method 6 – Using Macro with Loop Through Every n-th Row in Range
STEPS:
- Right-click on the active sheet named ‘n-th Row’. Select the option ‘View Code’.
- A blank VBA code window for that worksheet will open. Or press Alt + F11.
- Enter the following code in that code window:
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- Click Run or press the F5 key to run the code.
- The above code shades only the odd number rows from our dataset.
Read More: Excel VBA to Loop through Range until Empty Cell
Download the Practice Workbook
We can download the practice workbook from here.
Related Articles
- How to Use Range with Variable Row and Column with Excel VBA
- Excel VBA to Select Used Range in Column
- Excel VBA: Get Range of Cells with Values
- How to Use VBA to Set a Range Variable to Selection in Excel
- Excel VBA: Copy Dynamic Range to Another Workbook
