When you delete a row or column, excel updates all formula references to the
new addresses and replaces references to the deleted cells with #REF.

Any way to programatically disable this?

My specific problem: I've got a workbook that does a whole bunch of number
crunching based on a report pasted into one of the worksheets. The app that
generates that report (MS Money) now has a few more columns & rows I don't
need. I added code to delete this, but excel updates all the lookup
references into that sheet, thus throwing everything off. I looked at
updating all the references and avoiding the deletes, but this ends up being
too painful (lots of lookups on lookups).

So, my idea is to disable the reference updates, do all my deletions &
slashing on the one sheet, then re-enable reference updates, all the
references still pointing where they previously had.

Hi Rob,

there must be a better way. why not clear the cells rather than deleting
them?

But if you want to disable all formulas you can use Find & Replace and
replace all occurances of = with XX=

At the end rerun replacing XX= with =

XX= is just unlikely to be something in your workbook. You can change it to
anything you want. Save your work before you run this.
--
Thanks,
Shane Devenshire


"Rob McKaughan" wrote:

When you delete a row or column, excel updates all formula references to the
new addresses and replaces references to the deleted cells with #REF.

Any way to programatically disable this?

My specific problem: I've got a workbook that does a whole bunch of number
crunching based on a report pasted into one of the worksheets. The app that
generates that report (MS Money) now has a few more columns & rows I don't
need. I added code to delete this, but excel updates all the lookup
references into that sheet, thus throwing everything off. I looked at
updating all the references and avoiding the deletes, but this ends up being
too painful (lots of lookups on lookups).

So, my idea is to disable the reference updates, do all my deletions &
slashing on the one sheet, then re-enable reference updates, all the
references still pointing where they previously had.

"ShaneDevenshire" wrote:
there must be a better way. why not clear the cells rather than deleting
them?

Clearing doesn't help because it doesn't put the target data where the
references point to. For example, the old version of the MS Money report had
a budget difference column in D:D. The new version didn't have that column,
so what was in E:E in the old report was in D:D in the new. Clear would
still leave those columns in their places. Only deletes will match the new
report to the old layout.

But if you want to disable all formulas you can use Find & Replace and
replace all occurances of = with XX=

At the end rerun replacing XX= with =

Dang. I was hoping there was a Application object method that I missed.

I ended up taking the opportunity to rewrite the formulas that reference the
report to use MATCH() within VLOOKUP() to scan for the columns by name -
makes the formulas more tolerant of layout changes (they don't care where the
columns are anymore), and obfuscates the references enough so excel doesn't
mess with them.

Thanks!