Excel array formulas

[Guest]

I've "cleverly" written an array formula using a user defined function which
is implemented across several columns.

Now I want to (programmatically) delete some columns in the worksheet
according to certain conditions. Unfortunately I get an error message saying
that I can't change part of an array.

Do I have to seek out these rows, save the array formula (somehow), delete
the array formula, delte the columns, and re-create the array formula? or is
there something simpler?

Array formula is like {=myfunction(A10:j10,A43:j43,b1)}

REgards

Les

 

[Conrad Carlberg]

Hi Les,

Yup, the rules that apply to an array formula based on a UDF are the same as
those that apply to an array formula that's based on built-in functions and
formulas. You cannot change part of an array. Arrgh. And your description of
how to recover (saving the formula, etc.) looks to me like the best
approach, assuming that you need to retain the formula, so it can
recalculate. If you don't need to keep it as a formula, you could convert
the formula to values programmatically prior to deleting the columns.