Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>

    Regards
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Thank you Stuart
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Thank you Legare
    Regards
    Don

Posting Permissions

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