UDF: Count rows in named range

J

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??
 
J

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
 
C

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)
 
J

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??
Click to expand...


Figured it out.

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

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------
 
B

Bob Phillips

Function RowsInNamedRange(NamedRange As Range) As Long

RowsInNamedRange = NamedRange.Rows.Count

End Function
 
L

Leith Ross

Hello John,

You have declared the argument "NamedRange" as a range object. The code
works as worksheet function because you are passing the name of the
named range. Excel passes the contents of the cell to the function,
which in this case, is a Range object named "Test". If the contents are
not a Range object and only text, the function fails.

Example 1
------------------------------------------------
Defined named range is Test. Address is A2:A10
In cell A1 is the text "Test"
In B1 is the formula =RowsInNamedRange(A1)
Result in B1 is 9
------------------------------------------------

Example 2
------------------------------------------------
In cell A2 is the text "Test1"
This is not a defined range, only text
In B2 is the formula =RowsInNamedRange(A2)
Result in B2 is #NAME?
------------------------------------------------

In VBA the Range method expects an address in string format or the name
of a named range, and converts this into a Range object. To make the
function work in both environments, change it as shown below...

--------------------------------------------------
Function RowsInNamedRange(ByVal NamedRange As String) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
 
P

Peter T

change
Function RowsInNamedRange(NamedRange As Range) As Integer
Click to expand...
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
 
P

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
 
J

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
 
Top