dynamic range help requested

[Guest]

I am using XL 2002.

I want to use the following as the RefersTo value for the named range,
v_EchoCarts:

=OFFSET(r_EchoCart,1,0,COUNTA($P2:$P65536),1) [where r_EchoCart is a defined
name which refers to a range in the workbook - $P$1]. I am trying to make
v_EchoCarts dynamic.

In lieu of counting down all 65536 rows in the worksheet, is there any way
for me to use a worksheet function to determine the last row populated with
data in column P (i.e. replace P65536 in COUNTA($P2:$P65536))?

I can not tell you how much I have learned from this group since I started
to post here last Friday. Thank you all so very much.

TIA,

 

[JulieD]

Hi Joe

recommend you have a look at Debra Dalgleish's site on dynamic range names
(www.contextures.com/tiptech.html)

but basically try
=OFFSET(Sheet1!$P$1,1,0,Counta(Sheet1!$P:$P)-1,1)
should do what you want where Sheet1 is the name of the sheet you're dealing
with.

Cheers
JulieD

 

[Frank Kabel]

Hi
no need for this. Excel will check the used range (AFAIK)
automatically. So you won't gain a speed advantage.