# Thread: Number of rows in a named range. (VBA / Excel / 2000)

1. ## Number of rows in a named range. (VBA / Excel / 2000)

I need to determine the number of rows in a named range. I have developed the following function to do the task; but suspect that Mr Gates and Co. have probably provided a much more efficient solution.

Can anyone point me in the right direction?

<pre>
Public Function RangeRows(myRange)
Dim myLocn As String
Dim myTop As Long
Dim myBottom As Long
Dim i As Long

' Returns a string in the form of: ='MY WORKSHEET'!R2C1:R7C1
myLocn = ActiveWorkbook.Names(myRange).RefersToR1C1

' Locate first "R" following first "!".
' The number of the top row starts on the next character.
myTop = InStr(1, myLocn, "!")
myTop = InStr(myTop, myLocn, "R") + 1

' Locate next "C". The number of the top row ends there.
i = InStr(myTop, myLocn, "C")

' Define the top row of the range.
myTop = Val(Mid(myLocn, myTop, i - myTop))

' Locate next "R" . The number of the bottom row starts on
' the next character.
myBottom = InStr(i, myLocn, "R") + 1

' Locate next "C". The number of the bottom row ends there.
i = InStr(myBottom, myLocn, "C")

' Define the bottom row of the range.
myBottom = Val(Mid(myLocn, myBottom, i - myBottom))

RangeRows = myBottom - myTop + 1

End Function

</pre>

2. ## Re: Number of rows in a named range. (VBA / Excel / 2000)

A quick test using Excel 2003 suggests that <code>Range("rngName").Rows.Count</code>
will give the number you want. But if the range has a number of disjoint parts then it only returns the number of rows in the first one.

I assume the same code will work on Excel 2000

StuartR

3. ## Re: Number of rows in a named range. (VBA / Excel / 2000)

The code below will give you the number of rows in the named range MyRange. The code will give the sum of the rows in all of the areas. If the areas overlap, the overlaping rows will be counted twice.

<pre>Dim oRng As Range, lRows As Long
lRows = 0
For Each oRng In Range("MyRange").Areas
lRows = lRows + oRng.Rows.Count
Next oRng
</pre>

4. ## Re: Number of rows in a named range. (VBA / Excel / 2000)

Thank you Stuart

5. ## Re: Number of rows in a named range. (VBA / Excel / 2000)

Thank you Legare

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•