Excel 2010 Bible

Part VI: Programming Excel with VBA

892

Sub ProcessCells()

Dim Cell As Range

For Each Cell In Selection

If Cell.Value < 0 Then Cell.Value = Cell.Value * -1

Next Cell

End Sub

The preceding example works, but what if the selection consists of an entire column or an entire

range? This is not uncommon because Excel lets you perform operations on entire columns or

rows. In this case, though, the macro seems to take forever because it loops through each cell —

even those that are blank. What’s needed is a way to process only the nonblank cells.

You can accomplish this task by using the SelectSpecial method. In the following example,

the SelectSpecial method is used to create a new object: the subset of the selection that con-

sists of cells with constants (as opposed to formulas). This subset is processed, with the net effect

of skipping all blank cells and all formula cells.

Sub ProcessCells2()

Dim ConstantCells As Range

Dim Cell As Range

‘ Ignore errors

On Error Resume Next

‘ Process the constants

Set ConstantCells = Selection.SpecialCells _

(xlConstants, xlNumbers)

For Each Cell In ConstantCells

If Cell.Value < 0 Then Cell.Value = Cell.Value * -1

Next Cell

End Sub

The ProcessCells2 procedure works fast, regardless of what is selected. For example, you can

select the range, select all columns in the range, select all rows in the range, or even select the

entire worksheet. In all these cases, only the cells that contain constants are processed inside the

loop. This procedure is a vast improvement over the ProcessCells procedure presented earlier

in this section.

Notice that the following statement is used in the procedure:

On Error Resume Next

This statement causes Excel to ignore any errors that occur and simply to process the next state-

ment. This statement is necessary because the SpecialCells method produces an error if no

cells qualify and because the numerical comparison will fail if a cell contains an error value.

Normal error checking is resumed when the procedure ends. To tell Excel explicitly to return to

normal error-checking mode, use the following statement:

On Error GoTo 0