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

All that is returned for this:

Set r2 = r.Rows(1)

is the row, not the cells.


"Dave Unger" wrote:

Hello,

Ive thought about this one for a while, but havent been able to
reason out why. Havent 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

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

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

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.

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 (MVP - Excel)


"Dave Unger" wrote in message
...
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

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Dave Unger" wrote in message
...
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

Hi Rick,


On Sep 16, 1:57*am, "Rick Rothstein"
wrote:
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.

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 wrote:

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

--

Dave Peterson

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)


On Sep 16, 5:51*am, Dave Peterson wrote:
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)