Microsoft Office Tutorials and References

In Depth Information

**19.2 Defining a Range Object**

Set rng = Range("A1", "C5").Columns

MsgBox rng.Count ' displays 3

In this code, we alternately set
rng
to the collection of all cells, rows, and columns of the range

A1:C5. In each case,
MsgBox
reports the correct number of items in the collection. Note that the

Excel model does not have a cell, row, or column object. Rather, these objects are Range objects;

that is, the members of
rng
are Range objects.

When we do not specify the member type, a Range object acts like a collection of cells. To

illustrate, observe that the code:

Dim rng As Range

Set rng = Range("A1", "C5")

MsgBox rng.Count

MsgBox rng(6).Value ' row-major order

displays the number of cells in the range and then the value of cell 6 in that range (counted in

rowmajor order; that is, starting with the first row and counting from left to right). Also, the code:

Dim rng As Range

Dim oCell As Range

Set rng = Range("A1", "C5")

For Each oCell In rng

Debug.Print oCell.Value

Next

will cycle through each cell in the range
rng
, printing cell values in the Immediate window.

19.2 Defining a Range Object

As witness to the importance of the Range object, there are a total of 113 members (properties and

methods) throughout the Excel object model that return a Range object. This number drops to 51

if we count only
distinct
member names, as shown in
Table 19-2
.
(For instance, BottomRightCell

is a property of 21 different objects, as is TopLeftCell.)

Table 19-2. Excel
Members That Return a R
ange Object

_Default

End

Range

ActiveCell

EntireColumn

RangeSelection

BottomRightCell

EntireRow

RefersToRange

Cells

Find

Resize

ChangingCells

FindNext

ResultRange

CircularReference

FindPrevious

RowDifferences

ColumnDifferences

GetPivotData

RowRange

ColumnRange

Intersect

Rows

Columns

Item

SourceRange

CurrentArray

LabelRange

SpecialCells

CurrentRegion

Location

TableRange1

DataBodyRange

MergeArea

TableRange2

DataLabelRange

Next

ThisCell

DataRange

Offset

TopLeftCell