VBA Programming Series...32

Other Ways to Refer to a Range

The more you work with VBA, the more you realize that it’s a fairly well conceived language and is usually quite logical (despite what you may be thinking right now). Often, VBA provides multiple ways to perform an action. You can choose the most appropriate method for your problem. This section discusses some of the other ways to refer to a range.

This Article barely scratches the surface for the Range object’s properties and methods. As you work with VBA, you’ll probably need to access other properties and methods. The Help system is the best place to find out about them, but it’s also a good idea to record your actions and examine the code Excel generates.

The Cells property

Rather than use the VBA Range keyword, you can refer to a range via the Cells property.

Notice that I wrote Cells property, not Cells object or even Cells collection. Although Cells may seem like an object (or a collection), it’s really not. Rather, Cells is a property that VBA evaluates. VBA then returns an object (more spe-cifically, a Range object). If this seems strange, don’t worry. Even Microsoft appears to be confused about this issue. In some earlier versions of Excel, the Cells property was known as the Cells method. Regardless of what it is, just understand that Cells is a handy way to refer to a range.

The Cells property takes two arguments: row and column. Both of these argu-ments are numbers, even though we usually refer to columns by using let-ters. For example, the following expression refers to cell C2 on Sheet2:

Worksheets(“Sheet2”).Cells(2, 3)

You can also use the Cells property to refer to a multi-cell range. The follow-ing example demonstrates the syntax you use:

Range(Cells(1, 1), Cells(10, 10))

This expression refers to a 100-cell range that extends from cell A1 (row 1, column 1) to cell J10 (row 10, column 10).

The following statements both produce the same result; they enter a value of 99 into a 10-by-10 range of cells. More specifically, these statements set the Value property of the Range object:

Range(“A1:J10”).Value = 99

Range(Cells(1, 1), Cells(10, 10)).Value = 99

The advantage of using the Cells method to refer to ranges becomes apparent when you use variables rather than actual numbers as the Cells arguments. And things really start to click when you understand looping, which I cover in upcoming articles.

The Offset property

The Offset property provides another handy means for referring to ranges. This property, which operates on a Range object and returns another Range object, lets you refer to a cell that is a particular number of rows and col-umns away from another cell.

Like the Cells property, the Offset property takes two arguments. The first argument represents the number of rows to offset; the second represents the number of columns to offset.

The following expression refers to a cell one row below cell A1 and two col-umns to the right of cell A1. In other words, this refers to the cell commonly known as C2:

Range(“A1”).Offset(1, 2)

The Offset method can also use negative arguments. A negative row offset refers to a row above the range. A negative column offset refers to a column to the left of the range. The following example refers to cell A1:

Range(“C2”).Offset(-1, -2)

And, as you may expect, you can use 0 as one or both of the arguments for

Offset. The following expression refers to cell A1:

Range(“A1”).Offset(0, 0)

The Offset method is most useful when you use variables rather than actual values for the arguments. In upcoming article, I present some examples that demon-strate this.

Referring to entire columns and rows

If you need to refer to a range that consists of one or more entire columns, you can use an expression like the following:

Columns(“A:C”)

And to refer to one or more complete rows, use an expression like this:

Rows(“1:5”)