Excel Optimize

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.Color = vbRed
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 Range 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.Color = vbRed
End If
Next cell
‘ Process the formulas
Set FormulaCells = Selection _
.SpecialCells(xlFormulas)
For Each cell In FormulaCells
If cell.Value > 0 Then
cell.Interior.Color = vbRed
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 Home➪Editing➪ Find & Select➪Goto Special command, 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.