Hi Bpeltzer,

Defining the ranges like this helps in speed ...but as I use these
ranges in combo boxes ... I do not want 64000 empty lines in it ;o).

Cheers Sige

bpeltzer wrote:
What happens if you define the range as either 'Select Products'!C:O or
'Select Products'!C26:O65536? I don't know if that would help, but I'd at
least give it a shot. --Bruce

"Sige" wrote:

Hi There,

I have a NAME called "TESTRANGE"
='Select Products'!C26:O1500

I would like to make this range dynamic as the nr of items in the range
could vary a bit ...
I tried a couple of ways:
=OFFSET('Select Products'!$C$26,0,0,COUNTA('Select Products'!$B:$B),13)

=OFFSET('Select Products'!$C$26;0;0;Dynamic_Range;13)
with Dynamic_Range containing the nr of rows for the range (for above
eg: 1500)

=INDIRECT("'Select Products'!$C$26:$O$"&LROW)
with LROW-name containing the last row nr.

WHEN the range is not dynamic, my worksheet calculates my workbook in:
0.01 seconds
Actually I do not know, but let me say instantaneously!
WITH what ever of the above dynamic ranges I could chronometer the
calculation time ...
it took 8.0-8.2 seconds

I do not find the ranges particularly long ... and have just a couple
of them in my workbook, but the overhead to handle these volatile
functions seems to slow the calculation speed dramatically! (What
performance will this give in Excel 12?)

Luckily I kicked the array-formulas already out ...
Anybody suggestions on having dynamic ranges but not slowing down
calculation speed with 1000%?

Brgds Sige