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
 
 
Search JabSto ::




Custom Search