Page 198 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Defining a Range Using the Cells Property
Another method of referencing the same cell would be to specify the cell using the Cells
method. To do so, you would have to specify the row and column numbers of the cells in
opposite corners of the range you want to specify. For a single cell, you have to specify the
same cell twice; after all, it’s the same cell in opposite corners. For example, to select cell D6,
you would use the following code:

Range(Cells(4,6), Cells(4,6)).Select

Referencing Columns and Rows

Referring to an entire column or row is done using the Columns or the Rows property. You
Chapter 8
can select a single column or row or multiple columns or rows. This code snippet from the
Calculate_Table routine selects columns A through E and resizes them to fit the contents of
the cells.

Columns("A:E").Select
Columns.EntireColumn.AutoFit

Referencing Non-Contiguous Groups of Cells
Not all ranges you need to work with will be contiguous. You can specify non-contiguous
cells by separating the ranges with commas. For example, to select all of the sales data for
Mondays in January in the Y2001ByMonth.xls workbook, you could use the following code:
Range("D6:O6, D13:O13, D20:O20, D27:O27, D34:O34").Select.

Manipulating Groups of Cells

One reason for using a range of cells is to manipulate all of the cells within the range together
as one group rather than having to work with each cell individually. There are numerous
things that can be done to a group of cells collectively, such as resizing them, adding borders,
or changing the format of how the contents appear. You can even work with two groups of cells
together to generate either the union of the two groups or find where the two groups intersect.

Resizing Cells Programmatically
The default size for a cell has a width of 8.43 points and a height of 12.73 points. These set­
tings usually work just fine if you are working on a simple worksheet that’s not going to be
shared with anyone else. With a worksheet that is to be shared or that contains a good bit of
data, however, you might need to resize some of your cells.
To begin with, it is not possible to resize one particular cell. Any changes in width will affect
the entire column, and any changes in height will affect the entire row. Therefore, make any
changes done within a macro using either the Width property of the Column object or the
Height property of the Row object. You can set the Width and Height properties explicitly, if

172