COUNTCONTIGROWS is one of the FastExcel counting functions designed to be
used by the FastExcel Dynamic Range wizard.

To make it available as a free 'runtime' function it is also shipped with
Name Manager and is also available as a free downloadable addin from my
downloads page.

If you embed it in a Name you should generally make the reference absolute,
otherwise the reference depends on where the activecell is.

The function does not return a row number but the count of the number of
contiguous non-blank visible cells going downwards from the reference: this
is easier to use in OFFSET which is IMHO a better way to build dynamic
ranges than INDIRECT.

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

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