Solved: Deleting "0%" from Pie Charts

[jo15765]

I found this code here:

http://www.vbaexpress.com/forum/archive/index.php/t-27134.html

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


I modified the code slightly to this:

Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point
Dim k As Long

For k = 1 To 2
With Sheets(k)
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 With
Next k


Which I thought would cycle through to sheets, but I am getting an object variable or with block variable not set debug error.

[jo15765]

Or maybe I have to explicitly reference the charts? There are 4 on worksheet 1 and 2 on worksheet 2 that I am wanting this code to check....

[mdmackillop]

Refer to signature

[jo15765]

I am not sure what you mean?

[Vurtnebrak]

You can add a trendline or moving average to any data series in an unstacked, 2-D, area, bar, column, line, stock, xy (scatter), or bubble chart. A trendline is always associated with a data series, but a trendline does not represent the data of that data series. Instead, a trendline is used to depict trends in your existing data or forecasts of future data.

[jo15765]

The reason I am wanting to delete the "0" values is because on the charts, the customer has asked that we not show those. I was just trying to think of a way to automate the process as opposed to a manual delete each and every time. I found this code which will do the trick:

Sub CleanUpActiveChartLabels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim PieChart
Dim PieCharts

varPieCharts = Array("Chart 596", "Chart 9", "Chart 3", "Chart 2")

For Each PieChart In PieCharts
Sheets("Shee1").Select
ActiveSheet.ChartObjects(PieChart).Activate
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next
Next PieChart
End Sub

But now the problem I have is that I am finding the value is not really "0" it is "0.000000001" so those values are not getting deleted as needed. When I use the Macro Recorder this is the code it provides, but IDK how to custom tailor it to update the "Range" of cells?

Range("D75").Select
ActiveCell.FormulaR1C1 = "0%"
Range("D76").Select
ActiveCell.FormulaR1C1 = "0%"
Range("D79").Select
ActiveCell.FormulaR1C1 = "0%"
Range("D81").Select