The Range property

The Range property returns a Range object. If you consult the Help system for the Range property, you'll learn that this property has two syntaxes:

object.Range(celll) object.Range(cell1, cell2)

The Range property applies to two types of objects: a Worksheet object or a Range object. Here, celll and cell2 refer to placeholders for terms that Excel will recognize as identifying the range (in the first instance) and delineating the range (in the second instance). Following are a few examples of using the Range method.

You've already seen examples like the following one earlier in the chapter. The instruction that follows simply enters a value into the specified cell. In this case, it puts a 1 into cell A1 on Sheetl of the active workbook:

Worksheets("Sheet1").Range("A1").Value = 1

The Range property also recognizes defined names in workbooks. Therefore, if a cell is named Input, you can use the following statement to enter a value into that named cell:

Worksheets("Sheet1").Range("Input").Value = 1

The example that follows enters the same value into a range of 20 cells on the active sheet. If the active sheet is not a worksheet, this causes an error message:

ActiveSheet.Range("A1:B10").Value = 2

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

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:

And finally, this next example enters the value 4 into five cells: that is, a noncontiguous range. The comma serves as the union operator:

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 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, there is a much clearer way to access a cell relative to a range, called the Offset property. I discuss this property after the next section.

Was this article helpful?

0 0

Post a comment