Archive of Mr Excel Message Board

Check out Bill's new book on Charts and Graphs for Microsoft Office Excel 2007

Back to Charting for Excel archive index
Back to archive home

Chart

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.
=IF(B2="",IF(B2="",D2,""),IF(SUM(B2:B3)=B2,B2,""))
I tried the clear contents code I found posted, but maybe I did it wrong.
Private Sub CommandButton1_Click()

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



Re: Chart

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.

Dave

OzGrid Business Applications


Re: Chart

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.


Re: Chart

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.


Dave
OzGrid Business Applications


Re: Chart

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

Mark, Dave:

Thank you both. Problem solved.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.