count cells in range

[Dave Unger]

Hello,

I’ve thought about this one for a while, but haven’t been able to
reason out why. Haven’t found anything in the groups that quite
addresses this. Hopefully someone can straighten me out.

Set r = Range("A1:C3")
r.Count returns number of cells (9)

Set r2 = Intersect(r, Rows(1))
r2.Count returns number of cells (3)

but

Set r2 = r.Rows(1)
Now, r2.Count returns number of rows (1)

I now have to specify cells to get the cell count
r2.Cells.Count returns number of cells (3)

Thanks for your help,

regards,

Dave

 

[JLGWhiz]

All that is returned for this:

Set r2 = r.Rows(1)

is the row, not the cells.

 

[Dave Unger]

Hi JLGWhiz,

All that is returned for this:

Set r2 = r.Rows(1)

is the row, not the cells.

So why isn't Set r2 = Intersect(r, Rows(1)) the same? I'm missing a
subtle difference here.

Thanks for your reply,

Dave

 

[IanKR]

Hi JLGWhiz,

So why isn't Set r2 = Intersect(r, Rows(1)) the same? I'm missing a
subtle difference here.

Thanks for your reply,

Dave

I see - probably (and I'm guessing here!) because it's the intersection of a
range of *cells* with a range of a row, and therefore Count produces the
Count of the smallest unit, i.e. cells.

 

[Rick Rothstein]

The intersection of a square of cells... Range("A1:C3")... and an entire
row... Rows(1)... is only the range of cells they have in common...
Range("A1:C1"). When you ask for a count of that intersection, you are
asking the same thing as Range("A1:C1").Count

 

[Rick Rothstein]

Just to be clear about this... Rows(1) is the same as Range("A1:IV1") for
versions of Excel prior to XL2007 and Range("A1:XFD1") for XL2007. So the
Intersect function is finding the range of cells in common between
Range("A1:C3") and, for say XL2003, Range("A1:IV1"). That intersection is
the three-cell range Range("A1:C1"), whose Count property is 3.

 

[Dave Peterson]

Sometimes, Excel will guess wrong. If your range is an entirerow or an entire
column, it'll use (always???) use the number of rows or columns.

If you don't want excel to guess, be specific.

msgbox r.cells.count
msgbox r.columns.count
msgbox r.rows.count
msgbox r.areas.count
....

As a corollary, you may want to change things like:
dim myCell as range
dim myRng as range
set myrng = something
for each mycell in myrng
to:
for each mycell in myrng.cells
(or .columns or .rows or .areas or whatever you need)

 

[Dave Unger]

Hi Rick,

Just to be clear about this... Rows(1) is the same as Range("A1:IV1") for
versions of Excel prior to XL2007 and Range("A1:XFD1") for XL2007. So the
Intersect function is finding the range of cells in common between
Range("A1:C3") and, for say XL2003, Range("A1:IV1"). That intersection is
the three-cell range Range("A1:C1"), whose Count property is 3.

Thanks for your reply. Either I'm missing your point, or you're
missing mine.

Set r=range("A1:C3")

Set r2 = r.Rows(1)

The address of r2 is A1:C1, and I would expect r2.Count to return 3,
but it doesn't - it returns 1

regards,

Dave.

 

[Dave Unger]

Hi Dave,

That is the route I've been following (being specific), as I've
encountered this scenario a number of times. Thanks for your insight
on this.

regards,

Dave (Unger)

 

[Rick Rothstein]

Yes, I did miss your point. Let me try again then. Here is part of the
description of the Rows property from the VBA help files...

"For a Range object, returns a Range object that
represents the rows in the specified range."

Then, for these two statements..

Set r=range("A1:C3")
Set r2 = r.Rows(1)

The Rows property of the 'r' range returns the first row (because of the 1
in parentheses) of the range A1:C3. While it is true that the cells in that
range are A1, B1 and C1, the Rows property is not returning them
individually... Rows(1).Cells (that is, the cells in the first row) would do
that... it returns them as a single, horizontal group of cells... 1 row of
cells to be exact; hence, the Count property returns 1... for the count of
cells in the row, you would use r2.Cells.Count to get that.

--
Rick (MVP - Excel)


Hi Rick,

Just to be clear about this... Rows(1) is the same as Range("A1:IV1") for
versions of Excel prior to XL2007 and Range("A1:XFD1") for XL2007. So the
Intersect function is finding the range of cells in common between
Range("A1:C3") and, for say XL2003, Range("A1:IV1"). That intersection is
the three-cell range Range("A1:C1"), whose Count property is 3.

Thanks for your reply. Either I'm missing your point, or you're
missing mine.

Set r=range("A1:C3")

Set r2 = r.Rows(1)

The address of r2 is A1:C1, and I would expect r2.Count to return 3,
but it doesn't - it returns 1

regards,

Dave.

 

[Dave Unger]

Hi Rick,

Thanks for the explanation, I believe that cleared things up for me.
I guess the "rule" is to be aware of how one defines a range when
using range.count, or default to range.cells.count. What is
surprising to me is that no one else (to my knowlege) has ever
addressed this issue before.

regards,

Dave

 

[Dave Peterson]

It's come up a few times in the newsgroups.

But finding the question/answer via google would be difficult--what keyword(s)
should the search use???

 

[Dave Unger]

Hi, thanks to everyone for your help.

regards,

Dave Unger