Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 InsertNameDefine 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define name, count rows problem.
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 InsertNameDefine 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define name, count rows problem.
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" wrote in message ... 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 InsertNameDefine 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define name, count rows problem.
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" wrote in message ... 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 InsertNameDefine 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define name, count rows problem.
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" wrote in message ... 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" wrote in message ... 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 InsertNameDefine 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define name, count rows problem.
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" wrote in message ... 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" wrote in message ... 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 InsertNameDefine 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table - Can I pre-define rows and columns? | Excel Discussion (Misc queries) | |||
problem with Rows.Count and comparing cells | Excel Programming | |||
Problem with UsedRange.Rows.Count | Excel Programming | |||
problem using Set statement to define range object | Excel Programming | |||
Define Range in Deleting Empty Rows | Excel Programming |