3

I have many sheets with a graph on each, I would like to loop through each datalabel in each graph deleting any equaling 0, but can't seem to pull the datalabel value.

With ActiveChart
    For k = 1 To .SeriesCollection.Count
        For j = 1 To .SeriesCollection(k).Points.Count
            For l = 1 To .SeriesCollection(k).Points(j).DataLabels.Count
               If .SeriesCollection.Points(j).DataLabels(l) = 0 Then
                   .SeriesCollection.Points(j).DataLabel(l).Delete
                End If
            Next l
        Next j
    Next k
End With

(ignore the looping through each sheet activating each graph, that bit works so keeping code example minimal)

Can anyone advise how to loop datalabels, check the value, and delete where appropriate?

R3uK
  • 14,417
  • 7
  • 43
  • 77
Tim Wilkinson
  • 3,761
  • 11
  • 34
  • 62
  • If the data changes, then those labels will still be gone when they display non-zero values. Use a number format that suppresses zero, like `0;-0;;`. – Jon Peltier Nov 01 '21 at 13:32

1 Answers1

4

You were close!

You only missed the .Caption to check the value of the text inside the DataLabel!

I changed DataLabels(l) to DataLabels.Item(l) (you had an inconstancy in your code).

With ActiveChart
    For k = 1 To .SeriesCollection.Count
        For j = 1 To .SeriesCollection(k).Points.Count
            For l = 1 To .SeriesCollection(k).Points(j).DataLabels.Count
               If .SeriesCollection.Points(j).DataLabels.Item(l).Caption = 0 Then
                   .SeriesCollection.Points(j).DataLabels.Item(l).Delete
                End If
            Next l
        Next j
    Next k
End With

Final code used by OP:

With ActiveChart
    For k = 1 To .SeriesCollection.Count
        For j = 1 To .SeriesCollection(k).Points.Count
            If .SeriesCollection(k).Points(j).DataLabel.Caption = 0 Then
                .SeriesCollection(k).Points(j).DataLabel.Delete
           End If
        Next j
    Next k
End With
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • 1
    Couldn't get it working with `.Item(l)', however the `.Caption` was the key to getting it working, also there is only 1 `DataLabel` per `Point` so was able to remove one of the loops, thanks. – Tim Wilkinson Jan 31 '17 at 14:45
  • 1
    @TimWilkinson : Thx for your return, could you propose an edit on my answer with your working code? – R3uK Jan 31 '17 at 14:47
  • 1
    @TimWilkinson : I approved your edit to include your final code! ;) – R3uK Jan 31 '17 at 14:54