This tutorial explains how to use For Loop (For…Next Loop) to perform iteration in Excel VBA.
What is Loop?
Loop is repeating a set of actions. When you listen to a song repeatedly, it is said you are looping the song.
In VBA, there are different kinds of loop methods, they mainly differ in the way that how the loop is triggered and ended.
What is For Loop? (For…Next Loop)
For Loop (For…Next Loop) is the most frequently used loop, using For to begin loop, Next to end loop, Exit For to exit loop
Syntax: (codes in square brackets are optional)
For variable = start_value To end_value [Step step_value] your statement... [Exit For] Next [variable]
- – variable is a temporary name to get the loop started and to use it inside the loop statement. Usually programmer declare it as “i”, which standards for iteration, but it can be anything you like. I personally prefer to use i > j > k. For spreadsheet, I use c to stand for column, r to stand for row
- – start_value and end_value are where the variable begin and end. The difference between start_value and end_value are to indicate the number of loops to perform. For example, start_value is 1 and end_value is 3, then the code is to repeat 3 times, same for looping from 2 to 4.
- – step_value is the increment of variable after completing each loop. If you omit the Step statement, the default step_value is 1. At the end of the first loop, start_value is increased by 1, at the end of 2nd loop, start_value increase by 1 again, the loop stops until start_value + increment > end_value
- Similarly, negative step_value is to decrease variable. For example, when step_value = -1, start_value is decreased by 1 at the end of each loop until start_value-decrement < end_value
Example of simple For Loop
The following example fill A1 value as 1, A2 as 2, A3 as 3
Public Sub example1()
For r = 1 To 3
Range("A" & r).Value = r
Next r
End Sub
Result
Example of Exit For with If Else
In the below example, “If…Else” statement is inserted inside a loop to conditionally control exiting loop
Public Sub example2() For r = 1 To 3 If r = 3 Then Exit For Else Range("A" & r).Value = r End If Next r End Sub
Alternatively, you can write an “If” statement without “End If” in this way
Public Sub example2()
For r = 1 To 3
If r = 3 Then Exit For
Range("A" & r).Value = r
Next r
End Sub
Result
Example of For Loop using negative Step:
Since variable is decreased by 1 at the end of each loop, therefore start_value should be larger than end_value
Public Sub example3() For i = 10 To 1 Step -1 iArray(i) = i Next i End Sub
Negative step is especially useful in deleting row based on specific criteria, because you have to delete from the bottom to the top, but you cannot delete from top to the bottom. This is because if you loop from row 1,2,3,4, when you delete row 2, the old row 3 becomes new row 2, and you skip the loop on old row 3.
The below example delete all the rows where column E value is “Delete”.
Public Sub deleterow()
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If ActiveSheet.Range("E" & r).Value = "Delete" Then
ActiveSheet.Rows(r).Delete
End If
Next
End Sub
Other Applications of For Loop
Excel VBA change cell range to array
Excel VBA delete worksheet based on tab name
