Microsoft Office Tutorials and References

In Depth Information

The SQUARE function works with the upper-left cell in C5:C12 (which is C5) and returns the value squared.

Many Excel worksheet functions work in this way. For example, if you specify a multi-

cell range as the first argument for the LEFT function, Excel uses the upper-left cell in

the range. However, Excel is not consistent. If you specify a multicell range as the argu-

ment for the SQRT function, Excel returns an error.

The Cells property

Another way to reference a range is to use the Cells property. The Cells property accepts two arguments (a row

number and a column number) and returns a single cell. The following statement assigns the value in cell A1 to

a variable named FirstCell:

FirstCell = Cells(1, 1).Value

The following statement returns the upper-left cell in the range C5:D12:

UpperLeft = Range(“C5:D12”).Cells(1,1).Value

If you use the Cells property without an argument, it returns a range that consists of all

cells on the worksheet. In the following example, the TotalCells variable contains the

total number of cells in the worksheet:

TotalCells = Cells.Count

The following statement uses the Excel COUNTA function to determine the number of nonempty cells in the

worksheet:

NonEmpty =WorksheetFunction.COUNTA(Cells)

The Offset property

The Offset property (like the Range and Cells properties) also returns a Range object. The Offset property is

used in conjunction with a range. It takes two arguments that correspond to the relative position from the upper-

left cell of the specified Range object. The arguments can be positive (down or right), negative (up or left), or

zero. The following example returns the value one cell below cell A1 (that is, cell A2) and assigns it to a vari-

able named NextCell:

NextCell = Range(“A1”).Offset(1,0).Value