Using a For Each...Next Loop

Using a For Each...Next loop to iterate through a collection or array is faster than using an indexed loop. In most cases, using a For Each...Next loop is also more convenient and makes your macro smaller and easier to read and debug.

The following code is slow because it sets the row variable thisRow by calling r.Rows(i) each time through the loop.

Set r = Worksheets(1).Range("a1:a200")
For i = 1 To r.Rows.Count
    Set thisRow = r.Rows(i)
    If thisRow.Cells(1, 1).Value < 0 Then
        thisRow.Font.Color = RGB(255, 0, 0)
    End If
Next

The following code is faster and smaller because the For Each...Next loop keeps track of the row count and position.

For Each thisRow In Worksheets(1).Range("a1:a200").Rows
    If thisRow.Cells(1, 1).Value < 0 Then
        thisRow.Font.Color = RGB(255, 0, 0)
    End If
Next

For information about object variables, the With statement, and For Each...Next loops, see Chapter 3, "Controlling Program Flow," and Chapter 4, "Objects and Collections."