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: