Quirky graph problem

[roscoe]

I have a fairly complicated graph whose underlying data is macro built and all of the data is plotted using dynamic named ranges. The macro starts by wiping out all legacy data and rebuilding it based on the new input parameters. Up to now this has worked fine. Note that (1) this all runs in one macro and (2) one step in the macro is to redefine the dynamic named ranges with code (not that they change; I just never deleted that step).

I recently wrote a second macro to add 3 data series to the graph. This macro also blows away the old data (I'm guessing invalidating the dynamic named ranges) before rebuilding the new data (the graph series definitions remain based on the named ranges). Immediately after the macro runs the graph indicates an error in the graph data. I spent way too much time trouble shooting only to learn that if I simply save/close the file and reopen it the error goes away.

1) Is there a way to clear the error using code rather than close/reopen?
2) Will recreating the dynamic named ranges in the second macro fix the problem?

Otherwise, I may have to leave one row of data (to preserve the dynamic named ranges) and hope it all gets overwritten with valid data and leave behind a straggler.

Ideas?

 

[pbornemeier]

The error occurs because after you make the named ranges invalid, they are still referenced in the graph values.

If you delete the graph series definitions that use the named ranges, delete the named ranges, recreate those named ranges and recreate those graph series definitions, the errors you mentioned should not occur.

 

[roscoe]

I thought about that but was hoping for an easier solution.

Thanks

 

[roscoe]

Rather than blow away the series and recreate them with a ton of formatting tweaks, I just put some numbers in an out of the way place and before blasting the numbers and killing the range I simply redefine the range to this static location. Afterwards, I recreate the dynamic range. Seems to be working OK now...