I found Hide data label containing series name if value is zero on Super User but it removes data labels that have a value of 0
for all charts:
Sub RemoveZeroValueDataLabel()
'runs through every chart on the ActiveSheet
Dim cht As Chart
Dim chtObj As ChartObject
For Each chtObj In ActiveSheet.ChartObjects
Set cht = chtObj.Chart
Dim ser As Series
For Each ser In cht.SeriesCollection
Dim vals As Variant
vals = ser.Values
'include this line if you want to reestablish labels before deleting
ser.ApplyDataLabels xlDataLabelsShowLabel, , , , True, False, False, False, False
'loop through values and delete 0-value labels
Dim i As Integer
For i = LBound(vals) To UBound(vals)
If vals(i) = 0 Then
With ser.Points(i)
If .HasDataLabel Then
.DataLabel.Delete
End If
End With
End If
Next i
Next ser
Next chtObj
End Sub
I tried to edit it myself:
Sub RemoveZeroValueDataLabelonlyonechart()
Dim cht As Chart
Dim chtObj As ChartObject
Set cht = chtObj.Chart
Dim ser As Series
For Each ser In cht.SeriesCollection
Dim vals As Variant
vals = ser.Values
'include this line if you want to reestablish labels before deleting
ser.ApplyDataLabels xlDataLabelsShowLabel, , , , True, False, False, False, False
'loop through values and delete 0-value labels
Dim i As Integer
For i = LBound(vals) To UBound(vals)
If vals(i) = 0 Then
With ser.Points(i)
If .HasDataLabel Then
.DataLabel.Delete
End If
End With
End If
Next i
Next ser
End Sub
But this returns:
Microsoft visual basic | Run-time error '91' | Object variable or With block variable not set
How can I edit the code so it only removes data labels from the chart I have selected, not all charts in the sheet?
Dim chtObj As ChartObject
In the original loop chtObj loops on all the chart objects in the ActiveSheet. Here you want to set it only on a specific Chart object, so you removed the For
loop, fine. But your chtObj, which you defined as a reference to a ChartObject, references nothing up till now. You need to assign it to some Chart object. You need to know either the name or the index of the Chart object you want to modify. Then you will add one simple line after the one above:
Set chtObj = ActiveSheet.ChartObjects("someName")
or, if the chart is the first one created within that worksheet:
Set chtObj = ActiveSheet.ChartObjects(1)
After you add one of these two lines, with the appropriate name or number that corresponds to the target Chart, the rest of the code should work fine.