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

Topic

New Reply

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?

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

Reply | Quote

Replies

A quick test using Excel 2003 suggests that Range("rngName").Rows.Count
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

Reply | Quote

Thank you Stuart

Reply | Quote

A quick test using Excel 2003 suggests that Range("rngName").Rows.Count
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

Reply | Quote

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.

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

Reply | Quote

Thank you Legare

Reply | Quote

Thank you Legare

Reply | Quote

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.

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

Reply | Quote