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
Read More:Â How to Use For Next Loop in Excel VBA
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 For Each Loop in Excel VBA
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.
Read More:Â How to Use VBA for Each Cell in Range in Excel
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.
Read More:Â Excel VBA to Exit For Each Loop
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.
Bonjour,
Je voudrai connaitre comment utiliser For each pour donner une hauteur Précise en fonction de la couleur de la ligne d’une plage de donnée ou d’un tableau nommé.
Exemple ligne de couleur bleu aura une hauteur = 25
Ligne de couler rouge aura une hauteur = 20
Merci pour votre aide
Hello Amghar,
Thanks a lot for submitting Excel related problem at Exceldemy. Here you can use the VBA code mentioned below.
In that code, you need to mention the cell address inside the VBA code range property(as shown in the image). After this, if the condition satisfied, the cell row height will adjust accordingly.