Excel VBA For Loop / For…Next

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

loop_001

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

loop_002

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

Outbound References

http://msdn.microsoft.com/en-us/library/5z06z1kb.aspx

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *