Pie Charts - not including zero values

leifindia

New Member
Joined
Mar 8, 2005
Messages
13
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-

Code:
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

cata2200

Board Regular
Joined
Jul 29, 2006
Messages
60
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

New Member
Joined
Mar 8, 2005
Messages
13
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,753
Messages
5,524,664
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top