Looping through a range efficiently

Many macros perform an operation on each cell in a range, or they might perform selected actions based on each cell's content. These macros usually include a For-Next loop that processes each cell in the range.

The following example demonstrates how to loop through a range of cells. In this case, the range is the current selection. A variable named Cell refers to the cell being processed. Within the For-Next loop, the single statement evaluates the cell and changes its interior color if the cell contains a positive value.

Sub ProcessCells() Dim Cell As Range For Each Cell In Selection

If Cell.Value > 0 Then Cell.Interior.Colorlndex = 6 Next Cell End Sub

This example works, but what if the selection consists of an entire column or row? This is not uncommon because Excel lets you perform operations on entire columns or rows. In such a case, the macro seems to take forever because it loops through each cell in the selection — even the blank cells. To make the macro more efficient, you need a means for processing only the nonblank cells.

The following routine does just that by using the SpecialCells method. (Refer to the VBA Help system for specific details about its arguments.) This routine uses the Set keyword to create two new objects: the selection's subset that consists of cells with constants and the selection's subset that consists of cells with formulas. The routine processes each of these subsets, with the net effect of skipping all blank cells. Pretty slick, eh?

Sub SkipBlanks()

Dim ConstantCells As Range Dim FormulaCells As Range Dim cell As Range ' Ignore errors

On Error Resume Next ' Process the constants

Set ConstantCells = Selection _

.SpecialCells(xlConstants) For Each cell In ConstantCells

If cell.Value > 0 Then

cell.Interior.Colorlndex =


End If

Next cell

' Process the formulas

Set FormulaCells = Selection _


For Each cell In FormulaCells

If cell.Value > 0 Then

cell.Interior.ColorIndex =


End If

Next cell

End Sub

The SkipBlanks procedure works equally fast, regardless of what you select. For example, you can select the range, all columns in the range, all rows in the range, or even the entire worksheet. It's a vast improvement over the ProcessCells procedure presented earlier in this section.

Notice that I use the following statement in this code:

On Error Resume Next

This statement tells Excel to ignore any errors that occur and simply process the next statement (see Chapter 12 for a discussion of error handling). This statement is necessary because the SpecialCells method produces an error if no cells qualify.

Using the SpecialCells method is equivalent to choosing the EditOGo To command, clicking the Special button, and selecting the Constants option or the Formulas option. To get a feel for how this works, record your actions while you issue that command and select various options.

Was this article helpful?

+1 0


  • Veli-Matti
    How to loop through range of cells effectivly vba?
    6 months ago

Post a comment