Microsoft Office Tutorials and References

In Depth Information

**Working with Range Objects**

The next example produces exactly the same result as the preceding example:

Range(“A1”, “B10”) = 2

The sheet reference is omitted, however, so the active sheet is assumed. Also, the value property

is omitted, so the default property (which is
Value
for a
Range
object) is assumed. This example

also uses the second syntax of the
Range
property. With this syntax, the first argument is the cell

at the top left of the range, and the second argument is the cell at the lower right of the range.

The following example uses the Excel range intersection operator (a space) to return the

intersection of two ranges. In this case, the intersection is a single cell, C6. Therefore, this statement

enters
3
into cell C6:

Range(“C1:C10 A6:E6”) = 3

And finally, this next example enters the value
4
into five cells: that is, a noncontiguous range.

The comma serves as the union operator.

Range(“A1,A3,A5,A7,A9”) = 4

So far, all the examples have used the
Range
property on a
Worksheet
object. As I mentioned,

you can also use the
Range
property on a
Range
object. This concept can be rather confusing,

but bear with me.

Following is an example of using the
Range
property on a
Range
object. (In this case, the
Range

object is the active cell.) This example treats the
Range
object as if it were the upper-left cell in the

worksheet, and then it enters a value of
5
into the cell that
would be
B2. In other words, the

reference returned is relative to the upper-left corner of the
Range
object. Therefore, the statement that

follows enters a value of
5
into the cell directly to the right and one row below the active cell:

ActiveCell.Range(“B2”) = 5

I
said
this is
confusing. Fortunately, you can access a cell relative to a range in a much clearer

way: the
Offset
property. I discuss this property after the next section.

The Cells property

Another way to reference a range is to use the
Cells
property. You can use the
Cells

property, like the
Range
property, on
Worksheet
objects and
Range
objects. Check the Help

system, and you see that the
Cells
property has three syntaxes:

object.Cells(rowIndex, columnIndex)

object.Cells(rowIndex)

object.Cells