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:

ActiveSheet.Rows(i)

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")

rng.Select

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

Next

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:

rng.Columns.Count

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:

rng.Columns(3)

is column F, which does not intersect the range at all.