Pie Charts - not including zero values

[leifindia]

I've been trying to make a macro that creates standard pie charts from rows of data. One pie chart to a row, and the columns have the different types of values.
However I have many columns and in each row there will be many zero values in different columns, and because the pie chart includes all of these in the legend, I doesn't fit them all on.

Is there a way to make the pie chart ignore the zero values?

I hope this makes sense!


I'm using Excel 2007

Ellie

and this is a sample of the macro I am using to create the charts-

Sub Macro5()
'
' Macro5 Macro
'
    Range("A89:AM89").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'DATA'!$A$89:$AM$89")
    ActiveChart.ChartType = xlPie
    ActiveChart.SeriesCollection(1).XValues = "='DATA'!$E$2:$AM$2"
    Range("A90:AM90").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'DATA'!$A$90:$AM$90")
    ActiveChart.ChartType = xlPie
    ActiveChart.SeriesCollection(1).XValues = "='DATA'!$E$2:$AM$2"
    ActiveSheet.ChartObjects("Chart 27").Activate
    ActiveChart.ChartArea.Select
    Range("B1").Select
End Sub

 

[cata2200]

It can be a solution (not the best) to change the zero with NULL.
In this case the chart will not display the value, but it will assign the value in Legend.

It is a quick one, not the best solution.

Regards
Catalin

 

[leifindia]

Hi Catalin,

Thanks for your help, but my main problem is the legend. I need the chart area to be fairly small, so I only want the relevent titles in the legend.

Ellie