ExcelBanter - View Single Post

wrote...
....
I can easily reselect the range and change the formula. Then get errors
for the last two cells.
If I try to remove the now redundant cells I get a "You cannot change part
of an Array" error

In fact the only way I have to make all well seems to be delete and start
again.
If this the only way?

Simplifying the formula to =ROW(INDIRECT("1:8")), if you want this to
be variable in the number of rows, and the most rows you'd ever want
were 10, then you could use the defined name N to refer to the size of
the result, and you could enter the following array formula into a
10-row by 1 column range.

=IF(ROW(INDIRECT("1:10"))<=N,ROW(INDIRECT("1:"&N)) ,"")

For your particular formula, it'd be better to use nonarray formulas
like

X1:
=MAX(DataRange)

X2:
=IF(ROWS(X$1:X2)<=N,LARGE(DataRange,ROWS(X$1:X2)), "")

Select X2 and fill down as far as needed, e.g., into X3:X10.