An Overview Of Range.CountLarge

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:

MSDN CountLarge

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:

XL 2010 local helpfile

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.

Variant Type 32bit


About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , . Bookmark the permalink.

5 Responses to An Overview Of Range.CountLarge

  1. Doug Glancy says:

    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.

    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.

    Liked by 1 person

    • Colin Legg says:

      Hi Doug,

      Right, so taking the expression:

      • Range("A1:B2") returns a new Range object
      • then .EntireRow returns a new Range object
      • and then .Cells returns a new Range object
      • .Count gives 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 Range objects, 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.


  2. 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).



    • Doug Glancy says:

      @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.


  3. j7r7r7 says:

    “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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s