CODEWITHSUNDEEP
vbaexcelexcel-2010excel-charts
Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Excel chart VBA loop through datalabels

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?

Upvotes: 4

Views: 4602

Answers (1)

R3uK
R3uK

Reputation: 14537

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

Upvotes: 5

Related Questions