Have a formula yield a true blank that disconnects graph line

[Guest]

I use a formula to calculate values or use set criteria to yield blanks.
Then I graph the data and want the line graph to end where a blank occurs
rather than connect the line to zero where the blank occurs. Actually all
blanks are plotted as zeros, but I could enter -1 instead of blank and make
the scale minimum zero to eliminate this problem if the line did not connect
from the last data point to the -1 or zero.

This problem is not cured by copy>paste special>values even though the
blanks then no longer contain formula but look indistinguishable from real
blanks. But they do not behave as real blanks, for example, when hitting
end>down arrow.

 

[Andy Pope]

Hi,

If a cell contains something then the chart will plot it. Depending upon
the content what is plotted will vary.
Zero and text are plotted as zero.
NA() is not plotted - but if the option 'Not Plotted (leave gaps)' or
'interpolated' is used then the line will connect the valid points
either side. If 'Plot as zero' is used that is what is plotted, zero.

Have a look at this page for a possible work around.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

 

[Guest]

Thanks Andy, I will try this. I also came across a macro code:

Sub removeblanks()
Dim act As Worksheet
Set act = ActiveSheet
Sheets.Add After:=Sheets(act.Index)
ActiveSheet.Name = "formulas"
Row = 2
For Each cell In act.Range("a1").SpecialCells(xlCellTypeFormulas, 23)
If cell.Value <> "" Then
cell.Copy
Sheets("formulas").Range("a" & Row).PasteSpecial xlPasteValues
Row = Row + 1
End If
Next
End Sub

that I tried and it worked once and thereafter I can't get it to work again.
I get runtime error '1004' ; no cells were found.

I don't know whom to credit for this macro code, because I'm not sure where
I found it.

Owl