Richard,

I'm not sure about using UDF in names. But you can use this instead, as long as there is nothing
_below_ the block of rows starting in A10:

=OFFSET(Sheet1!$A$10,0,0,COUNTA(Sheet1!$A$10:$A$65 536),1)

HTH,
Bernie
MS Excel MVP


"Richard Buttrey" wrote in message
...
I'm trying to create a dynamic range name and have followed the
example on the excellent http://www.contextures.com/xlNames01.html
site.

I need to modify this since the named range is not the only stuff in
column A. I have non relevant stuff in say a1:A5 and want to create a
dynamic range starting in say A10, (and there might be other non
relevant stuff lower doiwn column A). So I thought I'd try the
COUNTCONTIGROWS function in an indirect function as below

=INDIRECT("A10:A"&COUNTCONTIGROWS(A10))

The name definition box seems to accept this OK, but when Igo and have
a look with the Insert Name Define it keeps changing the last A10
reference to what seems to be some sort of relative reference.

For instance, if my cursor happens to be in say B5 when I create the
name, if I put my cursor in B7 and examine the name, the last part has
changed to Sheet1A12.

If I use F5 to goto the range it highlights A1:A10

I've changed the A10 references to be absolute but this seems to make
no difference.

Can anyone suggest what's going on here please? I'm trying to make the
range variable to cover all the items in the contiguous range below
A10.

Usual TIA
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________