How to Use VBA for Each Row in a Range in Excel

While working with VBA in Excel, we often need it for iterating through each row of a range in a worksheet. In this article, I’ll show you how you can use Excel VBA to iterate through each row in a range effectively. You’ll learn to use VBA to iterate through each row of a range that contains both a single or multiple columns with proper examples and illustrations.

Use VBA for Each Row in a Range in Excel (Quick View)

How to Use VBA for Each Row in a Range in Excel: 2 Suitable Methods

So without further delay let’s move to our main discussion today.

First, we’ll learn to use VBA for each row a range that contains a single column.

Next, we’ll go for multiple columns.

1. Use VBA for Each Row in a Range in Excel with a Single Column

To use VBA for each row in a range, you have to use a for-loop to iterate through it.

For example, if your range is B4:B13, you can use:

For Each i In Range("B4:B13").Rows

Now the variable i will hold each row of the range B4:B13 one by one.

As each row contains a single column, you can use i directly to access the value of that row.

For example, to see the value of each row one by one, you can use:

MsgBox i

Obviously, you have to end the for-loop afterward by the line:

Next i

2. Use VBA for Each Row in a Range in Excel with Multiple Columns

If your range contains multiple columns, you can’t access the value from each row directly.

First, you have to start a for-loop, the same as the previous one.

For example, if your range is B4:D13, use:

For Each i In Range("B4:D13").Rows

Now the crucial part. You can’t access i directly, as each time it contains values from 3 columns (B, C, and D).

Therefore, to access a specific value each time, you have to specify the column number of that value. You can use the Cells property of VBA for this purpose.

For example, to display the value of the 3rd column one by one (Column D), you can use:

MsgBox i.Cells(1, 3)

And don’t forget to end the iteration with a Next statement.

Next i

Read More: Loop through a Range for Each Cell with Excel VBA

How to Use VBA for Each Row in a Range in Excel: 4 Practical Examples

Now we’ll see a few examples to use VBA to iterate through each row of a range in a worksheet.

Here we’ve got a worksheet with the names of some students and their marks in Physics and Chemistry of a school called Sunflower Kindergarten.

Our objective today is to use this data set to explore the examples.

Example 1: VBA to Collect Data from Each Row of a Single Column of a Given Range

Let’s try to collect the marks in Physics of all the students (C4:C13) to a new range F4:F13 with VBA.

As the range consists of a single column, we can access the value of each row directly here.

The VBA code will be:

⧭ VBA Code:

Sub Collecting_Data_from_a_Single_Column()

Output_Row = 1

For Each i In Range("C4:C13").Rows
    Range("F4:F13").Cells(Output_Row, 1) = i
    Output_Row = Output_Row + 1
Next i

End Sub

⧭ Output:

Run this code. It’ll collect the marks in Physics of each student (C4:C13) and copy them to the range F4:F13.

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

Example 2: VBA to Collect Data from Each Row of a Single Column of a Given Range with a Criterion

Now, we’ll collect data from a column of a given range with a criterion.

Let’s try to collect the marks in Physics of the students (C4:C13) who got more than 60 to a new range F4:F13.

As the range consists of a single column again, we can access the value of each row directly.

The VBA code will be:

⧭ VBA Code:

Sub Collecting_Data_from_a_Single_Column_with_Criterion()

Output_Row = 1

For Each i In Range("C4:C13").Rows
    If i > 60 Then
        Range("F4:F13").Cells(Output_Row, 1) = i
        Output_Row = Output_Row + 1
    End If
Next i

End Sub

⧭ Output:

Run this code. It’ll collect the marks in Physics of the students (C4:C13) who got more than 60 and copy them to the range F4:F13.

Example 3: VBA to Collect Data from Each Row of Multiple Columns of a Given Range

This time we’ll collect data from multiple columns of a given range.

Let’s try to collect the marks in Physics of all the students along with the names (B4:C13) to a new range (F4:G13).

Since the range involves two columns here (B4:C13), we have to use the Cells property of VBA.

The VBA code will be:

⧭ VBA Code:

Sub Collecting_Data_from_Multiple_Columns()

Row_Number = 1

For Each i In Range("B4:C13").Rows
    Range("F4:G13").Cells(Row_Number, 1) = i.Cells(1, 1)
    Range("F4:G13").Cells(Row_Number, 2) = i.Cells(1, 2)
    Row_Number = Row_Number + 1
Next i

End Sub

⧭ Output:

Run the code. It’ll collect the name of each student along with his / her marks in Physics to the range F4:G13.

Example 4: VBA to Collect Data from Each Row of Multiple Columns of a Given Range with a Criterion

Finally, we’ll collect data from multiple columns of a given range with a criterion.

Let’s try to collect the marks in Physics of all the students who got more than 60 along with the names (B4:C13) to a new range (F4:G13).

Since the range involves two columns here (B4:C13), we have to use the Cells property of VBA.

The VBA code will be:

⧭ VBA Code:

Sub Collecting_Data_from_Multiple_Columns_with_Criterion()

Row_Number = 1

For Each i In Range("B4:C13").Rows
    If i.Cells(1, 2) > 60 Then
        Range("F4:G13").Cells(Row_Number, 1) = i.Cells(1, 1)
        Range("F4:G13").Cells(Row_Number, 2) = i.Cells(1, 2)
        Row_Number = Row_Number + 1
    End If
Next i

End Sub

⧭ Output:

Run the code. It’ll collect the name of each student who got more than 60 in Physics along with the marks in the range F4:G13.

Download Practice Workbook

Download this practice article to exercise while you are reading this article.

Conclusion

So these are all about how to use VBA to iterate through each row in Excel. Do you have any questions? Feel free to ask us.

Related Articles

• How to Use For Next Loop in Excel VBA
• Excel VBA to Exit For Each Loop
• How to Use For Each Loop in Excel VBA