Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
In this procedure, Count starts out as 1 and then takes on values of 3 , 5 , 7 , and so on. The final
value of Count used within the loop is 99 . When the loop ends, the value of Count is 101 .
A Step value in a For-Next loop can also be negative. The procedure that follows deletes
Rows 2, 4, 6, 8, and 10 of the active worksheet:
Sub DeleteRows()
Dim RowNum As Long
For RowNum = 10 To 2 Step -2
Rows(RowNum).Delete
Next RowNum
End Sub
You may wonder why I used a negative Step value in the DeleteRows procedure. If you use a
positive Step value, as shown in the following procedure, incorrect rows are deleted. That’s because the
row numbers below a deleted row get a new row number. For example, when Row 2 is deleted, Row
3 becomes the new Row 2. Using a negative Step value ensures that the correct rows are deleted.
Sub DeleteRows2()
Dim RowNum As Long
For RowNum = 2 To 10 Step 2
Rows(RowNum).Delete
Next RowNum
End Sub
The following procedure performs the same task as the BadLoop example found at the
beginning of the “Looping blocks of instructions” section. I eliminate the GoTo statement, however,
converting a bad loop into a good loop that uses the For-Next structure.
Sub GoodLoop()
Dim StartVal As Integer
Dim NumToFill As Integer
Dim Cnt As Integer
StartVal = 1
NumToFill = 100
For Cnt = 0 To NumToFill - 1
ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt
Next Cnt
End Sub
For-Next loops can also include one or more Exit For statements within the loop. When this
statement is encountered, the loop terminates immediately and control passes to the statement
following the Next statement of the current For-Next loop. The following example
demonstrates use of the Exit For statement. This procedure determines which cell has the largest
value in Column A of the active worksheet:
 
Search JabSto ::




Custom Search