VBA to Clear Cells that Contain #N/A and also VBA to re populate formula in #N/A cells

Rather than remove the formula, could you just change it so that it doesn't return #N/A values?

If you put IFERROR([your formula],"")

this will return blank instead of #N/A value.

The workbook that you attached contains no formulas and there is no sheet named " Curve Data "

If you have formulas that return troublesome #NA values, have you tried replacing them with
=IFERROR(ExistingFormula, 0)

Hi Gerry,

I did a bit of research and found something on stackoverflow which might work. If you set the IFERROR formula as:

=IFERROR([your formula],#N/A)

then set your chart to show hidden or empty cells as gaps, this seems to ignore the #N/A values completely.

I tried it on a small sample of data I created and it seems to work

If I'm understanding you correctly, CA1 would either be a number or #N/A if some kind of lookup(?) fails?

I tried a very simple version of what you have (see attached) and the #N/A is always ignored on the charts. I even tried just typing #N/A into one of the lookup cells to see if that made a difference, but the chart still plots correctly, just ignoring the #N/A values.

However, if you are getting an error you could try:

=IFERROR(IF(AND(G5>=$CC$1,G5<=$CD$1),$CB$1,$CA$1),#N/A)

Ah, sorry, the penny just dropped!

You want something like this:

Chart Pic.png

I found this article where someone had managed to do it with a "mask". Can't say I've tried it but might be worth a go.

http://www.andypope.info/charts/brokenlines.htm

If not, we're back to the VBA route which is doable, but you might want to create a separate column for your chart data to save removing the formulas then having to put them back in. I'll have a look at creating some code for you.