Page 171 - Excel Progamming Weekend Crash Course

k540629 ch11.qxd 9/2/03 9:34 AM Page 146

146 Saturday Afternoon

that references the selected cells. You can use this Range object to manipulate the cells as
required by your program, for example, to format formula cells differently from other cells.

Table 11-1 Constants for the SpecialCells Method’s Type Argument

Constant Cells selected
xlCellTypeAllFormatConditions Cells of any format
xlCellTypeBlanks Blank cells
xlCellTypeComments Cells that contain comments

xlCellTypeConstants Cells containing number or text values
xlCellTypeFormulas Cells containing formulas
xlCellTypeLastCell The last cell in the range

xlCellTypeSameFormatConditions Cells having the same format
xlCellTypeVisible Cells that are visible

Table 11-2 Constants for the SpecialCells Method’s Value Argument

Constant Description
xlErrors Formula cells with an error
xlLogical Cells containing a logical formula
xlTextValues Cells containing text data

xlNumbers Cells containing number data

The following are examples of using the SpecialCells method. If the worksheet cursor
is positioned in a table of data, this code activates the last (lower right) cell in the table.
ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLastCell). _
Activate

The CurrentRegion property was explained in Session 10.

Cross-Ref
This code sets all cells in the range r1 that contain numerical data to 0:

r1.SpecialCells(xlCellTypeConstants, xlNumbers).Value = 0
The program in Listing 11-2 presents a more sophisticated example of using the
SpecialCells method. Within the worksheet region that is selected, it formats all cells