Remove zero chart labels

[ChloeRadshaw]

So this piece of code should remove any empty labels in a pie chart - However notice that if the data label is removed and the report rerun then the data label is not put back.

Adding .Points(iPts).HasDataLabel = False results in the formatiting of the labels being lost.

This is a complete PAIN. Does anyone have any ideas on how to add / remove labels on pie charts please.


[VBA]
Option Explicit

Sub remove0Labels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point


If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, _
"No Chart Selected"
Else
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
'dataLabelType = .DataLaboels.Type.xlValue

nPts = .Points.Count
aVals = .values
For iPts = 1 To nPts
Set po = .Points(iPts)
If aVals(iPts) = 0 Then
'po.DataLabel.NumberFormat
.Points(iPts).HasDataLabel = False
Else

End If
Next
End If

End With
Next
End If
End Sub
[/VBA]

[Bob Phillips]

[vba]

Sub remove0Labels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point


If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, _
"No Chart Selected"
Else
For Each srs In ActiveChart.SeriesCollection
With srs
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
Set po = .Points(iPts)
.Points(iPts).HasDataLabel = aVals(iPts) <> 0
Next
End With
Next
End If
End Sub
[/vba]

[ChloeRadshaw]

I will try this - But i remember doing this and seeing that the formatting of the labels was lost which is strange.

Is there an easy way of getting and then setting the format of the data labels.

Finally this also does not solve the value being in the legend which is a shame...

[ChloeRadshaw]

Anybody have any other ideas here?

This did not work...

[ChloeRadshaw]

This is a dream come true...

http://www.andypope.info/charts/piezeros.htm