Microsoft Office Tutorials and References
In Depth Information
Figure 19-1. A noncontiguous range
is the Range object that refers to the i th column of the active worksheet (and is a collection of the
cells in that column). Similarly:
refers to the i th row of the active worksheet.
The Columns and Rows properties can also be used with a Range object. Perhaps the simplest way
to think of rng.Columns is as the collection of all columns in the worksheet reindexed so that
column 1 is the leftmost column that intersects the range rng . To support this statement, consider
the following code, whose results are shown in Figure 19-1 :
Dim i As Integer
Dim rng As Range
Set rng = Range("D1:E1, G1:I1")
MsgBox "First column in range is " & rng.Column ' Displays 4
MsgBox "Column count is " & rng.Columns.Count ' Displays 2
For i = -(rng.Column - 2) To rng.Columns.Count + 1
rng.Columns(i).Cells(1, 1).Value = i
Figure 19-1. A noncontiguous range
Note that the range rng is selected in Figure 19-1 (and includes cell D1). The Column property of
a Range object returns the leftmost column that intersects the range. (Similarly, the Row property
returns the topmost row that intersects the range.) Hence, the first message box will display the
number 4.
Now, from the point of view of rng , Columns(1) is column number 4 of the worksheet (column
D). Hence, Columns(0) is column number 3 of the worksheet (column C) which, incidentally, is
not part of rng . Indeed, the first column of the worksheet is column number
-(rng.Column - 2)
which is precisely why we started the For loop at this value.
Next, observe that:
is equal to 2 (which is the number displayed by the second message box). This is a bit unexpected.
However, for some reason, Microsoft designed the Count property of r ng .Columns to return
the number of columns that intersect only the leftmost area in the range, which is area D1:E1. (We
will discuss areas a bit later.) Finally, note that:
is column F, which does not intersect the range at all.
Search JabSto ::

Custom Search