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
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
Search JabSto ::

Custom Search