XY Charts - not plotting zero or #N/A Points

[paulch]

Using Excel 2003

Have a range of data containing some "empty" cells. However, as they
come from a formula they aren't of course empty.

Am using this data to plot an X-Y chart where I want these "empty"
cells not to plot. Have set the tools-options-charts setting to Not
plotted.

Have read numerous other postings here and elsewhere which says to have
#N/A as my "empty" value. It still interpolates the points.

Seen a posting suggesting I use VBA but unless this is relatively
simple, is a bit beyond me (and can't find a posting with any code)

Any suggestions?

Thanks

Paul

 

[Andy Pope]

Hi,

This timely post might help.
http://peltiertech.com/WordPress/mind-the-gap-charting-empty-cells/

Not that even with #N/A the scatter line will not be broken, only the
data points are skipped.
http://www.andypope.info/charts/brokenlines.htm

The VBA would be something like,

Range("A1:A10").specialcells(xlcelltypeformulas,16).clear

which will clear all cells in the range with formula errors.

Cheers
Andy

 

[paulch]

Thanks Andy

Got a colleague to use your VBA and it works a treat

Thanks very much

Paul