Define name, count rows problem.

I would like to count the number of rows in a named range that contains
multiple areas on the same worksheet. To setup the range, I held the Control
key down while selecting the three different areas and then selected
Insert>Name>Define to establish the �data� range. The following code yields
10 rows and not the expected 21. It appears the code only considers the first
part of the named range and not the entire string.
Any help would be appreciated.
Thanks
Sommer

Names in workbook:
data

Refers to:
=Sheet1!$A$3:$M$12,Sheet1!$A$25:$M$30,Sheet1!$A$40:$M$44

Macro code:
Set CheckArea = Range(�data�)
NumberOfRows = CheckArea.Rows.Count

Hi,

If all areas are below each other or on separate sets of rows (ie, no rows
in common),
eg: { B1:G10, C15:F20, Z30:AB50 } ,
but not { B1:G10, Z5:BA50} where rows 5:10 appear in both areas
then

Dim r as range, ttl as long
Set CheckArea = Range(“data”)
For Each r in CheckAreas.Areas
ttl=ttl+r.rows.count
Next
Msgbox ttl

- In all cases, you should be able to do (using loop through all rows):

Dim r as range, rResult as range
Set CheckArea = Range(“data”)
Set rResult=checkarea.rows(1)

'Create range of unique entire rows
For Each r in CheckAreas.Rows
set rResult=Application.union(rResult,r.entirerow)
Next

'Count rows for each range
For Each r in rResult.Areas
ttl=ttl+r.rows.count
Next
Msgbox ttl

--
Regards,
Sébastien


"sommer" wrote:

> I would like to count the number of rows in a named range that contains
> multiple areas on the same worksheet. To setup the range, I held the Control
> key down while selecting the three different areas and then selected
> Insert>Name>Define to establish the “data” range. The following code yields
> 10 rows and not the expected 21. It appears the code only considers the first
> part of the named range and not the entire string.
> Any help would be appreciated.
> Thanks
> Sommer
>
> Names in workbook:
> data
>
> Refers to:
> =Sheet1!$A$3:$M$12,Sheet1!$A$25:$M$30,Sheet1!$A$40:$M$44
>
> Macro code:
> Set CheckArea = Range(“data”)
> NumberOfRows = CheckArea.Rows.Count
>

Hi Sommer,

Based on a suugestion by Dana DeLouis, try:

Sub Tester03()

Dim i As Long

i = Intersect(Range("Data").EntireRow, _
Columns(1)).Cells.Count

MsgBox i

End Sub


---
Regards,
Norman



"sommer" <[email protected]> wrote in message
news:[email protected]...
>I would like to count the number of rows in a named range that contains
> multiple areas on the same worksheet. To setup the range, I held the
> Control
> key down while selecting the three different areas and then selected
> Insert>Name>Define to establish the "data" range. The following code
> yields
> 10 rows and not the expected 21. It appears the code only considers the
> first
> part of the named range and not the entire string.
> Any help would be appreciated.
> Thanks
> Sommer
>
> Names in workbook:
> data
>
> Refers to:
> =Sheet1!$A$3:$M$12,Sheet1!$A$25:$M$30,Sheet1!$A$40:$M$44
>
> Macro code:
> Set CheckArea = Range("data")
> NumberOfRows = CheckArea.Rows.Count
>

nice, much simpler :-)
--
Regards,
Sébastien


"Norman Jones" wrote:

> Hi Sommer,
>
> Based on a suugestion by Dana DeLouis, try:
>
> Sub Tester03()
>
> Dim i As Long
>
> i = Intersect(Range("Data").EntireRow, _
> Columns(1)).Cells.Count
>
> MsgBox i
>
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "sommer" <[email protected]> wrote in message
> news:[email protected]...
> >I would like to count the number of rows in a named range that contains
> > multiple areas on the same worksheet. To setup the range, I held the
> > Control
> > key down while selecting the three different areas and then selected
> > Insert>Name>Define to establish the "data" range. The following code
> > yields
> > 10 rows and not the expected 21. It appears the code only considers the
> > first
> > part of the named range and not the entire string.
> > Any help would be appreciated.
> > Thanks
> > Sommer
> >
> > Names in workbook:
> > data
> >
> > Refers to:
> > =Sheet1!$A$3:$M$12,Sheet1!$A$25:$M$30,Sheet1!$A$40:$M$44
> >
> > Macro code:
> > Set CheckArea = Range("data")
> > NumberOfRows = CheckArea.Rows.Count
> >
>
>
>

Hi Sommer,

To add,

> It appears the code only considers the first
> part of the named range and not the entire string.

See 'Areas Collection Object' in VBA help. See particularly the comments
immediately preceding the help example.

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Sommer,
>
> Based on a suugestion by Dana DeLouis, try:
>
> Sub Tester03()
>
> Dim i As Long
>
> i = Intersect(Range("Data").EntireRow, _
> Columns(1)).Cells.Count
>
> MsgBox i
>
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "sommer" <[email protected]> wrote in message
> news:[email protected]...
>>I would like to count the number of rows in a named range that contains
>> multiple areas on the same worksheet. To setup the range, I held the
>> Control
>> key down while selecting the three different areas and then selected
>> Insert>Name>Define to establish the "data" range. The following code
>> yields
>> 10 rows and not the expected 21. It appears the code only considers the
>> first
>> part of the named range and not the entire string.
>> Any help would be appreciated.
>> Thanks
>> Sommer
>>
>> Names in workbook:
>> data
>>
>> Refers to:
>> =Sheet1!$A$3:$M$12,Sheet1!$A$25:$M$30,Sheet1!$A$40:$M$44
>>
>> Macro code:
>> Set CheckArea = Range("data")
>> NumberOfRows = CheckArea.Rows.Count
>>
>
>

Norman
Thanks for your help, problem solved.

"Norman Jones" wrote:

> Hi Sommer,
>
> To add,
>
> > It appears the code only considers the first
> > part of the named range and not the entire string.
>
> See 'Areas Collection Object' in VBA help. See particularly the comments
> immediately preceding the help example.
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi Sommer,
> >
> > Based on a suugestion by Dana DeLouis, try:
> >
> > Sub Tester03()
> >
> > Dim i As Long
> >
> > i = Intersect(Range("Data").EntireRow, _
> > Columns(1)).Cells.Count
> >
> > MsgBox i
> >
> > End Sub
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "sommer" <[email protected]> wrote in message
> > news:[email protected]...
> >>I would like to count the number of rows in a named range that contains
> >> multiple areas on the same worksheet. To setup the range, I held the
> >> Control
> >> key down while selecting the three different areas and then selected
> >> Insert>Name>Define to establish the "data" range. The following code
> >> yields
> >> 10 rows and not the expected 21. It appears the code only considers the
> >> first
> >> part of the named range and not the entire string.
> >> Any help would be appreciated.
> >> Thanks
> >> Sommer
> >>
> >> Names in workbook:
> >> data
> >>
> >> Refers to:
> >> =Sheet1!$A$3:$M$12,Sheet1!$A$25:$M$30,Sheet1!$A$40:$M$44
> >>
> >> Macro code:
> >> Set CheckArea = Range("data")
> >> NumberOfRows = CheckArea.Rows.Count
> >>
> >
> >
>
>
>