Excluding zero values from chart plot

[Stretchwickster]

I have a range of cells which include zero and non-zero values. I would like to plot all the non-zero values - is there an easy way to do this (i.e. without sifting through each cell? Clive

 

[RobBroekhuis]

What kind of plot? Rob

 

[Stretchwickster]

Sorry - I'll explain a bit more. I have plotted an xlColumnClustered chart of the data but it includes the zero values, which mess up the accuracy of the chart as most of the non-zero values are between 90 and 100. The effect of the zero values is to push the non-zero values to the very top of the chart which means you can't read them too well. Any suggestions Rob? Clive

 

[RobBroekhuis]

If that's your main concern, then just set the axis range to one that pleases you (you could do this programmatically). If the gaps where the zero data is are troublesome, then I think you'll have to do some quick programming to duplicate the range with the zero values left out. Not too much work, really. Sound reasonable?
Rob

 

[xlbo]

Set Tools>Options>Chart>Plot Empty Cell As : Not Plotted
Then select the data range and run this

For each c in selection
if c.value = 0 then
c.clearcontents
else
end if
next Rgds
~Geoff~