I’ve had a few questions and comments about the Range.CountLarge property so I thought it was high time that I give you my take on it.
What Is It?
Here’s how Range.CountLarge is described over at MSDN:
Yep, it’s exactly the same as Range.Count except that it can handle larger collections. The description, ‘Returns a value that represents the number of objects in the collection’, is fairly abstract, so herein follows my understanding of it.
The Range.CountLarge property returns the count of cells or rows or columns of a given Range object, depending on how that Range object was created.
If the Range object was created using something like the Range.Rows or Range.EntireRow properties then the Range object represents a collection of rows and Range.CountLarge will return the count of those rows.
Sub foo()
Dim rng As Range
Set rng = Sheet1.Range("A1:E2").Rows
Debug.Print rng.CountLarge 'returns 2
Set rng = Sheet1.Range("A1:D5").EntireRow
Debug.Print rng.CountLarge 'returns 5
End Sub
It’s a similar story for columns.
If the Range object was created using something like the Worksheet.Range or Range.Cells properties then the Range object represents a collection of cells and Range.CountLarge will return the count of those cells.
Sub foo()
Dim rng As Range
Set rng = Sheet1.Range("A1:E2")
Debug.Print rng.CountLarge 'returns 10
Set rng = Sheet1.Range("A1:D5").EntireRow.Cells
Debug.Print rng.CountLarge 'returns 81920
End Sub
VBA Help File Topic Error
Doug commented on an error in the Excel VBA help file’s Range.CountLarge property topic – and rightly so. Here’s how my Excel 2010 local help file describes it:
This is a really poor piece of information. Range.CountLarge absolutely does not count the largest value in a given range of values. Heck, what does that even mean?!
Why Does It Return A Variant?
The MSDN remarks explain that the Range.Count property – which returns a Long – may throw an overflow error in Excel 2007 and later because the size of Excel’s grid was increased to 17,179,869,184 cells. Long data types in VBA are signed 32-bit integers, so the largest positive number they can hold is 2,147,483,647.
To accommodate the larger grid, the Range.CountLarge property effectively returns a signed 64-bit integer which can hold positive values up to 9,223,372,036,854,775,807. In VBA the data type for this is a LongLong, but LongLongs are only valid on 64-bit platforms. Therefore, to accommodate 32-bit platforms, the return type had to be defined as a Variant. If you run the below code you will either see Variant/<Unsupported variant type> or Variant/LongLong in the Locals window, depending on your platform.
RAD Excel 
Aha! There’s some interesting stuff going on behind the scenes there.
I had never noticed that Range…Count changes depending on the last object in the chain, e.g., Range(“A1:B2”).Count vs
Range(“A1:B2”).EntireRow.Count vs.
Range(“A1:B2”).EntireRow.Cells.Count.
I guess that means that the EntireRow collection has a default property of Row. I’m pretty literal, so I always specify what I’m counting.
LikeLiked by 1 person
Hi Doug,
Right, so taking the expression:
Range("A1:B2").EntireRow.Cells.CountRange("A1:B2")returns a newRangeobject.EntireRowreturns a newRangeobject.Cellsreturns a newRangeobject.Countgives a count from the last one so it is counting the cells.Although they may have different internal collections of cells or rows which means they enumerate differently, ultimately they are all still
Rangeobjects, which means that they all have the same default member which is _Default.If you write a method which takes in a range as a parameter and you need to enumerate that range then you would have to explicitly call Rows, Cells or Columns (or whatever) to create a new range to be sure that you are enumerating it how you want.
LikeLike
Hi Colin
Given that we can use Count or CountLarge to find out the number of cells in a range, is there a built-in way of referring to any cell in a range using a single value? e.g. for a 7 by 7-cell range (= 49 cells), I would like to specify cell 48 and return a reference to the cell on the 7th row, in the 6th column (6*7 = 42 + 6 = 48). Obviously this would assume counting by rows as opposed to by columns (say).
regards
LikeLike
@johnharoldbrown, try typing “? Range(“A1:G7″).Cells(48).Address” in the Immediate Window. The result is F7. Note that it will continue past the number of cells in the range, e.g., ? Range(“A1:G7”).Cells(50).Address yields A8.
LikeLike
“Count” doesn’t always count the number of cells in the range; it really counts the number of subranges. For a regular range, or when you use the .Cells property with no parameters, each cell is its own subrange. When you use .Rows, each row (or part thereof) is its own subrange.
The “.Row” property gives the index of the row, not a reference to the row itself.
LikeLike