How to delete the empty series on the Legend?

[mlee3]

I would like to delete the empty series on the legend. So far I've 6 seriers and two of them are empty. I got the runtime error - unable to get the legendEntries property of legend code class when running the following codes. It looks like the first empty series went through well but the secomd empty series got the error message. Please help!!!

Sub UpdateChart()
Dim c As Chart: Set c = activechart
Dim i As Integer
Dim a As Variant
c.HasLegend = False
c.HasLegend = True
For i = 1 To 6
a = c.SeriesCollection(i).Values
If WorksheetFunction.Sum(a) = 0 Then
c.Legend.LegendEntries(i).Delete
End If
Next i
End Sub

 

[shannondbaker]

I don't know if you still need help with this, but your code helped me out and I wanted to return the favor. The problem with your code is you don't take in account the fact that once you delete the legend entry, your available legend entries change to fill in the void. For instance, if you delete legend entry 4, then legend entry 6 becomes legend entry 5 and 5 becomes the new 4. A counter will correct the situation (see my modification to your code below):

I would like to delete the empty series on the legend. So far I've 6 seriers and two of them are empty. I got the runtime error - unable to get the legendEntries property of legend code class when running the following codes. It looks like the first empty series went through well but the secomd empty series got the error message. Please help!!!

Sub UpdateChart()
Dim c As Chart: Set c = activechart
Dim i As Integer
Dim a As Variant
c.HasLegend = False
c.HasLegend = True
n = 0 'Counter
For i = 1 To 6
a = c.SeriesCollection(i).Values
If WorksheetFunction.Sum(a) = 0 Then
c.Legend.LegendEntries(i-n).Delete 'Added in Counter
n = n+1 'Counter
End If
Next i
End Sub