For Next Loop

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The For.. .Next loop is used when you know how many times you want to repeat a group of statements. The syntax of a For.. .Next loop looks like this:

For counter = start To end [Step increment] statementl statement2 statements Next [counter]

The code in the brackets is optional. Counter is a numeric variable that stores the number of iterations. Start is the number at which you want to begin counting. End indicates how many times the loop should be executed.

For example, if you want to repeat the statements inside the loop five times, use the following For statement:

For counter = 1 To 5

Your statements go here


When Visual Basic encounters the Next keyword, it will go back to the beginning of the loop and execute the statements inside the loop again, as long as counter hasn't reached the end value. As soon as the value of counter is greater than the number entered after the To keyword, Visual Basic exits the loop. Because the variable counter automatically changes after each execution of the loop, sooner or later the value stored in counter exceeds the value specified in end.

By default, every time Visual Basic executes the statements inside the loop, the value of the variable counter is increased by one. You can change this default setting by using the Step clause. For example, to increase the variable counter by three, use the following statement:

For counter = 1 To 5 Step 3 Your statements go here Next counter

When Visual Basic encounters the above, it executes the statements inside the loop twice. The first time in the loop, counter equals 1. The second time in the loop, counter equals 4 (3 + 1). After the second time inside the loop, counter equals 7 (4+3). This causes Visual Basic to exit the loop.

Note that the Step increment is optional. Optional statements are always shown in square brackets (see the syntax at the beginning of this section). The Step increment isn't specified unless it's a value other than 1. You can place a negative number after Step. Visual Basic will then decrement this value from the counter each time it encounters the Next keyword.

The name of the variable (counter) after the Next keyword is also optional. However, it's good programming practice to make your Next keywords explicit by including counter.

How can you use the For.. .Next loop in a Microsoft Excel spreadsheet? Suppose in your sales report you'd like to include only products that were sold in a particular month. When you imported data from a Microsoft Access table, you also got rows with the sold amount equal to zero. How can you quickly eliminate the "zero" rows? Although there are many ways to solve this problem, let's see how you can handle it with the For.. .Next loop.

1. In the Visual Basic window, insert a new module into the current project and rename it ForNextLoop.

2. Enter the following procedure in the ForNextLoop module:

Sub DeleteZeroRows() Dim totalR As Integer Dim r As Integer

Range("A1").CurrentRegion.Select totalR = Selection.Rows.Count Range("B2").Select

If ActiveCell = 0 Then

Selection.EntireRow.Delete totalR = totalR - 1


ActiveCell.Offset(1, 0).Select End If Next r End Sub

3. Switch to the Microsoft Excel window and prepare the following spreadsheet:




Product Name

Sales (in Pounds)



















4. Run the DeleteZeroRows procedure.

Let's examine the DeleteZeroRows procedure line by line. The first two statements calculate the total number of rows in the current range and store this number in the variable totalR. Next, Visual Basic selects cell B2 and encounters the For keyword. Because the first row of the spreadsheet contains the column headings, decrease the total number of rows by one (totalR-1). Visual Basic will need to execute the instructions inside the loop six times.

The conditional statement (If...Then...Else) nested inside the loop tells Visual Basic to make a decision based on the value of the active cell. If the value is equal to zero, Visual Basic deletes the current row and reduces the value of totalR by one. Otherwise, the condition is false, so Visual Basic selects the next cell. Each time Visual Basic completes the loop, it jumps to the For keyword to compare the value of r with the value of totalR-1. When the procedure ends, the sales spreadsheet does not include products that were not sold.

Tip 6-4: Paired Statements

For and Next must be paired. If one is missing, Visual Basic generates the error message "For without Next."

Was this article helpful?

0 0

Post a comment