Other Ways to Refer to a Range

OTHER WAYS TO REFER TO A RANGE

There are other ways to refer to a range in VBA.

The Cells property

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

The Cells property takes two arguments: row and column. For example, the following expression refers to cell D2 on Sheet2:

    WorkSheets("Sheet2").Cells(2,4)

You can also use the Cells property to refer to a multicell range. The following shows 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 32 into a 10-by-10 range of cells, by setting the Value property of the Range object:

    Range("A1:J10").Value = 32
    Range(Cells(1,1), Cells(10,10)).Value = 32

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.

The Offset property

The Offset property provides another way for referring to ranges.

It operates on a Range object and returns another Range object and lets you refer to a cell that is a particular number of rows and columns away from another cell.

The Offset property has two arguments. The first argument represents the number of rows to offset, and the second represents the number of columns to offset.

The following expression refers to a cell one row below cell A1 and two columns to the right of cell A1, i.e. cell C2:

    Range("A1").Offset(1,2)

The Offset property 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)

The following example refers to cell A2:

    Range("C2").Offset(0,-2)

The Offset property is most useful when you use variables instead of actual values for the arguments.

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

    Columns("A:C")

And to refer to one or more complete rows, use

    Rows("1:5")