UDF: Count rows in named range

[John]

I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------
Function RowsInNamedRange(NamedRange As Range) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

 

[JE McGimpsey]

If you want to pass the name of the range, one way:



Public Function RowsInNamedRange(ByVal sName As String) As Long
On Error Resume Next
RowsInNamedRange = _
ActiveWorkbook.Names(sName).RefersToRange.Rows.Count
End Function

 

[Chip Pearson]

Since NamedRange is already a range, you don't need to wrap it up in a
Range object. E.g., use


RowsInNameRange=NameRandge.Rows.Count

If, however, you wanted to pass the name of a range as a string, use

Function RowsInNamedRange(NamedRange As String) As Long
RowsInNamedRange=Range(NamedRange).Rows.Count
End Function

Since the number of rows in a worksheet is greater than the number
that can be stored in an Integer type variable, you should use As Long
instead of As Integer.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[John]

I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------
Function RowsInNamedRange(NamedRange As Range) As Integer

    RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

Figured it out.

-------------------------------
Public Function RowsInNamedRange(NamedRange As String)

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

 

[Dave Peterson]

?rowsinnamerange worksheets("somesheetnamehere").range("rngnamedrange1")

 

[Bob Phillips]

Function RowsInNamedRange(NamedRange As Range) As Long

RowsInNamedRange = NamedRange.Rows.Count

End Function

 

[Peter T]

change

Function RowsInNamedRange(NamedRange As Range) As Integer

to
Function RowsInNamedRange(NamedRange As String) As Long

be sure to pass the name in quotes unless the argument is passed as a cell
ref containing the string-name.

There's no advantage to using As Integer vs As Long and Integer would fail
if the value is +32k (eg a whole column or rows).

Regards,
Peter T

 

[Peter T]

How strange, when I posted I didn't see any other replies, a few minutes
later after having done so suddenly there's a whole bunch of them !

Peter T

 

[John]

Thank you all for the considerate responses. For others' reference, I
ended up using the code below to return the row count of a given named
range.

------------------------------------------------
Public Function RowsInNamedRange(NamedRange As String)

On Error Resume Next
RowsInNamedRange = Range(NamedRange).Rows.Count

End Function