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)

On Tue, 6 Jan 2009 12:52:15 -0800 (PST), John
wrote:

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