Microsoft Office 2000/Visual Basic Programmer's Guide

The Range Property

You will use the Range property to return a Range object in many different circumstances. The Application object, the Worksheet object, and the Range object all have a Range property. The Application object's Range property returns the same Range object as that returned by the Worksheet object. In other words, the Application object's Range property returns a reference to the specified cell or cells on the active worksheet. The Range property of the Range object has a subtle difference that it is important to understand. Consider the following example:

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

Set rng1 = Application.Range("B5")
Set rng2 = Worksheets("Sheet1").Range("B5")
Set rng3 = rng2.Range("B5")

The three Range objects do not all return a reference to the same cell. In this example, rng1 and rng2 both return a reference to cell B5. But rng3 returns a reference to cell C9. This difference occurs because the Range object's Range property returns a reference relative to the specified cell. In this case, the specified cell is B5. Therefore, the "B" means that the reference will be one column to the right of B5, and the "5" means the reference will be the fifth row below the row specified by B5. In other words, the Range object's Range property returns a reference to a cell that is n columns to the right and y rows down from the specified cell.

You will typically use the Range property to return a Range object, and then use the properties and methods of that Range object to work with the data in a cell or group of cells. The following table contains several examples illustrating usage of the Range property.

To do this	Use this code
Set the value of cell A1 on Sheet1 to 100	`Worksheets("Sheet1").Range("A1").Value = 100`
Set the value for a group of cells on the active worksheet	`Range("B2:B14").Value = 10000`
Set the formula for cell B15 on the active worksheet	`Range("B15").Formula = "=Sum(B2:B14)"`
Set the font to bold	`Range("B15").Font.Bold = True`
Set the font color to green	`Range("B15").Font.Color = RGB(0, 255, 0)`
Set an object variable to refer to a single cell	`Set rngCurrent = Range("A1")`
Set an object variable to refer to a group of cells	`Set rngCurrent = Range("A1:L1")`
Format all the cells in a named range	`Range("YTDSalesTotals").Font.Bold = True`
Set an object variable to a named range	`Set rngCurrent = Range("NovemberReturns")`
Set an object variable representing all the used cells on the Employees worksheet	`Set rngCurrent = Worksheets("Employees").UsedRange`
Set an object variable representing the group of related cells that surround the active cell	`Set rngCurrent = ActiveCell.CurrentRegion`
Set an object variable representing the first three columns in the active worksheet	`Set rngCurrent = Range("A:C")`
Set an object variable representing rows 3, 5, 7, and 9 of the active worksheet	`Set rngCurrent = Range("3:3, 5:5, 7:7, 9:9")`
Set an object variable representing multiple noncontiguous groups of cells on the active sheet	`Set rngCurrent = Range("A1:C4, D6:G12, I2:L7")`
Remove the contents for all cells within a specified group of cells (B5:B10) while leaving the formatting intact	`Range("B5", "B10").ClearContents`

As you can see from the examples in the preceding table, the Cell argument of the Range property is either an A1-style string reference or a string representing a named range within the current workbook.

You will also use the Range property to return Range objects as arguments to other methods in the Excel object model. When you use the Range property in this way, make sure you fully qualify the Worksheet object to which the Range property applies. Failing to use fully qualified references to the Range property in arguments for Excel methods is one of the most common sources of error in range-related code.