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)

Download Practice Workbook

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

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

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

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

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: VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)

Similar Readings

• Excel VBA: Loop Through Columns in Range (5 Examples)
• Excel VBA: Copy Dynamic Range to Another Workbook
• How to Use VBA to Select Range from Active Cell in Excel (3 Methods)

4 Effective Examples to Use VBA for Each Row in a Range in Excel

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.

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.

Similar Readings

• Excel Macro: Sort Multiple Columns with Dynamic Range (4 Methods)
• Excel VBA to Loop through Range until Empty Cell (4 Examples)
• How to Convert Range to Array in Excel VBA (3 Ways)   

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.

Things to Remember

Here I’ve used the Cells property of VBA which is a property of the VBA Range object. If you want to know more about the VBA Range object, visit this link.

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. And don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• How to Use Range with Variable Row and Column with Excel VBA
• Excel VBA to Select Used Range in Column (8 Examples)
• Excel VBA: Get Range of Cells with Values (7 Examples)
• How to Use VBA to Set a Range Variable to Selection in Excel (5 Methods)
• How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)