List referenced columns in formulas?

[Maury Markowitz]

I have a LARGE workbook that consists of a large block of data copied
out of our database (the "input block"), and a set of formulas that
combine the values to produce user-readable results (the "user
block").

I don't know beforehand what columns of data in the input block will
be referred to by formulas in the user block, that's up to the user.
What I would like to do is remove any unreferenced columns in the
input block, after the input block is copied in and recalculations
complete.

I can't seem to find any way to "get all the column references used in
this range" sort of method. Any pointers?

Maury

 

[JLGWhiz]

Check out the Precedents and Dependents properties. Maybe you can work with
that.

 

[Maury Markowitz]

Check out the Precedents and Dependents properties.  Maybe you can workwith
that.

As it turns out, you can! The one annoyance is that Precedents returns
an error instead of null if there are none in the range, which means
you have to use a trap. Other than that it's pretty easy to use.
Simply loop over the area you're interested in, ask for a range's
Precedents.Areas, and record which columns are in use. Then delete the
ones that aren't. Excel's formula re-writer fixes everything up as
they're removed.

It cut my spreadsheet by about 10% in size.

Maury