Page 170 - Excel Progamming Weekend Crash Course

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

Session 11 — Working with Columns, Rows, and Cells 145

Screen Updating

By default, any changes that a program makes to a worksheet are immediately
reflected on the screen (only if the worksheet is visible, of course). When
there’s no need for the user to see the changes as they are made, you can turn
screen updating off. This has the added advantage of making your programs run
faster. To do so, set the Application.ScreenUpdating property to False at the
start of a program. Be sure to set it back to True when the program is done.

When you run the program, be sure that all cells in the selected range contain numerical
values. Blank cells or text entries will fool the program.

Referencing by Cell Position

The third way to reference cells with the Cells property is by the position of the cell in the
range. The syntax is:

Cells(CellPosition)
CellPosition is a numerical value specifying the position of the cell in the range. Cells
are numbered starting at 1 for the top left cell, and move across and down. For example, in
a range that is four columns wide and three rows high, the cells in the first row is, from left
to right, positions 1, 2, 3, and 4. The next row is 5, 6, 7, and 8, and the last row is 9, 10,
11, and 12.
This syntax for the Cells property does not offer any advantages over the row and col-
umn addressing described in the previous section. To use this syntax to iterate through all
cells in a range, use the following code (assume that r is the range of interest):

For pos = 1 To r.Columns.Count * r.Rows.Count
‘ Code to work with r.Cells(pos) goes here.
Next pos

The SpecialCells Method

You can use the SpecialCells method to select certain cells within a range, based on their
contents and other characteristics. You can then perform an operation on only the selected
cells. The syntax is:

SomeRange.SpecialCells(Type, Value)
The Type argument is required and identifies the type of cells to be selected. Permitted
values for this argument are listed in Table 11-1. The Value argument is optional and used
only when Type is either xlCellTypeConstants or xlCellTypeFormulas. Permitted values
for this argument are given in Table 11-2. The SpecialCells method returns a Range object