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




On Jan 6, 2:11*pm, Leith Ross
wrote:
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
---------------------------------------------------
Sincerely,
Leith Ross

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:http://www.thecodecage.com/forumz/member.php?userid=75
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=47231