VBA provides the Exit For statement to exit a For loop prematurely. For instance, the code in Example 8-1 finds the first nonempty cell in the first row of the active worksheet. If none exists, a message is displayed. Note the use of a Boolean variable to keep track of the existence question.

Example 8-1. Finding the First Nonempty Cell

Sub FindFirstNonEmpty() Dim oCell As Range Dim bNone As Boolean bNone = True

For Each oCell In ActiveSheet.Rows(1).Cells

If Not IsEmpty(oCell) Then oCell.Select bNone = False Exit For End If Next

If bNone Then MsgBox "No nonempty cells in row 1", vbInformation End Sub

We can also control the step size and direction for the counter in a For loop using the Step keyword. For instance, in the following code, the counter i is incremented by 2 each time the block of code is executed:

' code block goes here Next i

The following loop counts down from 10 to 1 in increments of -1. This can be useful when we want to examine a collection (such as the cells in a row or column) from the bottom up:

' code block goes here Next i

Was this article helpful?

0 0

Post a comment