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,
>
> 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
>

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" <(E-Mail Removed)> wrote in message
news:4cd92e30-0b8a-4157-9f7a-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:4cd92e30-0b8a-4157-9f7a-(E-Mail Removed)...
>> 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
>>
>

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 Rick,


On Sep 16, 1:57*am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> 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.

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 <peter...@verizonXSPAM.net> wrote:
> Sometimes, Excel will guess wrong. *If your range is an entirerow or anentire
> 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)
>

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)


"Dave Unger" <(E-Mail Removed)> wrote in message
news:0050df8b-a1ce-4e39-adc3-(E-Mail Removed)...
Hi Rick,


On Sep 16, 1:57 am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> 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.