Microsoft Office Tutorials and References
In Depth Information
Using Ranges
The Count property
The Count property returns the number of cells in a range. The following function uses the
Count property:
Function CELLCOUNT(rng)
CELLCOUNT = rng.Count
End Function
The following formula returns 9 :
=CELLCOUNT(A1:C3)
The Count property of a Range object is not the same as the COUNT worksheet
function. The Count property returns the number of cells in the range, including empty cells
and cells with any kind of data. The COUNT worksheet function returns the number of
cells in the range that contain numeric data.
Excel 2007 and later worksheets contain over 17 billion cells compared with a mere
17 million in previous versions. Because of this dramatic increase, the Count property —
which returns a Long — may return an error if there are more than 2,147,483,647 cells
to be counted. You can use the CountLarge property instead of Count to be safe, but
beware that CountLarge does not work in older versions of Excel. In the CELLCOUNT
function, the following statement will handle any size range (including all cells on a
worksheet):
CELLCOUNT = rng.CountLarge
The Parent property
The Parent property returns an object that corresponds to an object’s container object. For a
Range object, the Parent property returns a Worksheet object (the worksheet that contains
the range).
The following function uses the Parent property and returns the name of the worksheet of the
range passed as an argument:
Function SHEETNAME(rng)
SHEETNAME = rng.Parent.Name
End Function
 
Search JabSto ::




Custom Search