Dynamic range name - odd results
 

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
__________________________

Dynamic range name - odd results
 

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
__________________________

Dynamic range name - odd results
 

Thanks Bernie,

To be perfectly honest I hadn't realised CountContigRows was a UDF.
After checking it seems to be a function in the Name Manager add-in
from Jan Karel Pieterse - it seems to disappear when I unload that
Add-in.

Unfortunately there is stuff in other rows below the block starting in
A10. I guess the only way is to use a helper function or create some
variable name that I know is below the block in question, and use
these in a variant of the Offset function you describe.

Many thanks,



On Fri, 28 Jul 2006 08:31:57 -0400, "Bernie Deitrick" <deitbe @
consumer dot org wrote:

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$6 5536),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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Dynamic range name - odd results
 

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
__________________________

Dynamic range name - odd results
 

Hello Charles,

Thanks for that point about it being an absolute cell. I thought I'd
originally tried that as one option, but I've just tried it again and
it seems to work just fine: i.e.

=OFFSET(Sheet1!$A$10,0,0,COUNTContigRows(Sheet1!$A $10:$A$10),1)

One last question please. Does the CountContigRows function 'carry
across' with the workbook such that a user who doesn't have the Add-In
installed on their PC, will still find it works. I guess the answer is
no.

I was at the recent XL conference in London and found your session on
speeding up macros extremely interesting and thought provoking.
Thanks.

Regards


On Fri, 28 Jul 2006 20:29:46 +0100, "Charles Williams"
wrote:

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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Dynamic range name - odd results
 

Hi Richard

If you want to get the range from A10 to the cell above the next blank
you can use native Excel functions,

=A10:INDEX(A10:A65536,MATCH(TRUE,(A10:A65536=""),0 )-1)

confirmed with CTRL+SHIFT+ENTER

or with just enter

=A10:INDEX(A10:A65536,MATCH(TRUE,INDEX(A10:A65536= "",0),0)-1)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=565932