Which Range Am I Counting?

Steven asks a good question, which stems from James Cane’s comment

Ever wondered why the Count property returns the number of columns of a range which was obtained from the Columns property? After all, in a regular range, Count returns the number of cells.

The answer is that each Range object contains an internal flag which specifies if it’s a cells range, a columns range or a rows range. The behaviour of Count, Item and For-Each are then modified appropriately. Clever, really.

I always assumed that, rather than an internal flag, it was an interface. In my imagination there’s a Range class with a bunch of different interfaces. If you called the IAreas interface by using the Areas property, IAreas would inherit most properties and methods from the Range class, but others it would control on its own, like Count. However, a quick check of the Object Browser reveals an IRange interface, but no IArea, IColumn, or IRow. Maybe it really is an internal flag, but why would it be internal? Why wouldn’t they expose it as a property? I’d like to know for my own edification, but that’s not Steven’s question.

Rather, the question is how to determine what you’re counting when you get a Range object in code. If, for instance, your function has a Range object argument, how can you determine what Count is returning? Count of columns, rows, areas, cells?

I guess the right answer is to always specify what you want. If rng As Range is your argment, then use


depending on what you need. If you have multiple Areas, the Rows and Columns properties will only work on the first Area, so there’s more work to do, again depending on what you need. I can’t say I’ve ever worked this hard for the Count property, but maybe I should. Am I missing something?

Posted in Uncategorized

2 thoughts on “Which Range Am I Counting?

  1. How strange – some months after I wrote the comment prior to the one mentioned, I changed job – one of my colleagues at the new place was James Cane! I even remember him explaining the convoluted (overloaded) Range methods. Even stranger (but only slightly) we also attended the same school, although he passed through there considerably later (by a decade or two) than I.

  2. Thanks for the info – it was driving me crazy that I had to qualify COUNT with ROWS, COLUMNS or CELLS to be sure of what a range object was returning. After all, the ADDRESS is the same in all cases. It seems to me that these properties would be more useful if they were range collections of individual rows, columns or cells…

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.