The Range object represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. The Range object exposes three properties that can be used to manipulate your worksheets via VBA.

• The Range property of a Worksheet or Range class object
• The Cells property of a Worksheet object
• The Offset property of a Range object

Named Ranges

Before going any farther we’ll briefly get into what a named range is. In an Excel Workbook, you can give a particular cell or range of cells a name. This way your programming becomes easier. You can refer to these names in your formulas. If you create a name for a cell in Sheet1, you can go to Sheet2 and use it in a formula. The scope of named ranges in Worksheet by default. To see a list of existing names you’ve already created, go to Formulas > Name Manager. You can also go to Formulas > Use in Formula. To create a named range, first select the cell or range you want to name. Click inside the Name box to the left of the formula bar, which is at the top of the worksheet, below the menu bar.

Range Property

The Range property returns a Range object. This property has two different syntaxs. It also applies to two types of objects: a Worksheet object or a Range object. Below is the format of the two syntaxs

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

Entering Values into a Cell or Range of Cells

The following lines of code show you different ways to enter values into cells. The named range “MyNamedRange” refers to cell B5 in Sheet2. If you specify a sheet other than Sheet2, you get an error. Perhaps you are better off not specifying a sheet at all and just beginning the line of code with “Range…”. Note that the first two lines (7.2 and Jack) will execute and will not be rolled back when an error occurs on line 3.

Sub RangeExamples()
    ' The user is on Sheet3 when they run this.
    Worksheets("Sheet1").Range("A1").Value = 7.2
    Worksheets("Sheet1").Range("A2").Value = "Jack"  ' can enter text also
    Worksheets("Sheet2").Range("MyNamedRange").Value = 45.1
    ' If the active sheet isn’t a worksheet, the statement below causes an error message
    ActiveSheet.Range("A3:B4").Value = 2
    Range("A3", "B4") = 2  ' same as line above
End Sub

The next two lines of code serve as two more examples. The first line uses the intersection operator (a space) to return the intersection of two ranges, which in this case is B7. The second example enters the value 9 in five cells, that are non-contiguous.

Range("B1:B10 A7:E7") = 3
Range("A1,A3,A5,A7,A9") = 9

All the examples have used the Range property on a Worksheet object. As mentioned before, you can also use the Range property on a Range object. The following line of code treats the Range object as if it were the upper-left cell in the worksheet, and then it enters a value of 5 in 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

You can access a cell relative to a range in a much clearer way: the Offset property.

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.