MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by rosie on February 28, 2001 2:07 PM

I am trying to delete the contents of cells to keep a chart line from going to zero if a cell is emptied by a formula.
I tried the clear contents code I found posted, but maybe I did it wrong.
Private Sub CommandButton1_Click()

Selection.QueryTable.Refresh BackgroundQuery:=False
Selection.QueryTable.Refresh BackgroundQuery:=False
If Range("C2") = "<1" Then Range("C2").ClearContents
:: End Sub

Posted by David Hawley on February 28, 2001 3:10 PM

Hi Rosie

If you go to my web site and look at the links "Chart Tips and Tricks" and "Dynamic Named Ranges" you my see some ideas that you could adopt.


OzGrid Business Applications

Posted by Mark W. on February 28, 2001 3:52 PM

Rosie, if you ask me you're making this far more
difficult that need be. Use #N/A rather than "".

Look what happens when you attempt to chart
=SERIES("Y",,{1,2,"",4},1). It changes to
=SERIES("Y",,{1,2,0,4},1). That's your problem.

Now try =SERIES("Y",,{1,2,#N/A,4},1). Look! No
problem. #N/A in a chart series allows allow you
to interpolate a line across data gaps.

Posted by David Hawley on February 28, 2001 6:10 PM

Mark, I hope you didn't pinch idea from my web page :o)

As Mark has said (and my web page) Excel will not plot #N/A. Or =NA(). It also will not plot hidden Rows/Columns.

OzGrid Business Applications

Posted by Rosie on March 01, 2001 4:54 AM

Mark, Dave:

Thank you both. Problem solved.