Range Property

The Range property applies to the Application, Range, and Worksheet objects. Note that:

Application.Range is equivalent to:


When Range is used without qualification within the code module of a worksheet, then it is applied to that sheet. When Range is used without qualification in a code module for a workbook, then it applies to the active worksheet in that workbook.

Thus, for example, if the following code appears in the code module for Sheet2:

Worksheets(1).Activate Range("D1").Value = "test"

then its execution first activates Sheet1, but still places the word "test" in cell D1 of Sheet2. Because this makes code difficult to read, I suggest that you always qualify your use of the Range property.

The Range property has two distinct syntaxes. The first syntax is:


where Name is the name of the range. It must be an A1-style reference and can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). Any dollar signs in Name are ignored. We can also use the name of a named range.

To illustrate, here are some examples:

Range("A2:F3 A1:D5") ' An intersection Range("A2:F3, A1:D5") ' A union

Of course, we can use the ConvertFormula method to convert a formula from R1C1 style to A1 style before applying the Range property, as in:

Range(Application.ConvertFormula("R2C5:R6C9", xlR1C1, xlA1)) Finally, if TestRange is the name of a range, then we may write:


Range(Application.Names!TestRange) to return this range.

The second syntax for the Range property is: object.Range(Cell1, Cell2)

Here Cell1 is the cell in the upper-left corner of the range and Cell2 is the cell in the lower-right corner, as in:

Alternatively, Cell1 and Cell2 can be Range objects that represent a row or column. For instance, the following returns the Range object that represents the second and third rows of the active sheet:

It is important to note that when the Range property is applied to a Range object, all references are relative to the upper-left corner cell in that range. For instance, if rng represents the second column in the active sheet, then:


is the second cell in that column, and not cell A2 of the worksheet. Also, the expression:


represents the (absolute) cell C2, because this cell is in the second column and second row from cell B1 (which is the upper-left cell in the range rng ).

0 0

Post a comment