How to update Excel chart legend without showing zero legend entry values

If you do not wish to display the series names that don't have values in the chart legend (see pie chart example below), then you can use a similar code as this one below:

Sub update_legend()

chartname = "Mychart"
ActiveSheet.ChartObjects(chartname).Activate
On Error GoTo aa:     'code to delete the current legend if there is one    
ActiveChart.Legend.Select    
Selection.Delete
aa:    ActiveChart.SetElement (msoElementLegendRight) 'code to create a new legend on the right of the charta = 0
With ActiveChart.SeriesCollection(1)
ChartValues = .Values
    For i = 1 To .Points.Count 'loop to go through all the values of the chart
        If ChartValues(i) = "" Or ChartValues(i) = 0 Then 'if the value is zero or empty...
            ActiveSheet.ChartObjects(chartname).Chart.Legend.LegendEntries(i - a).Select
            a = a + 1
            Selection.Delete  '... then delete the corresponding legend entry.
        End If
    Next
End With
End Sub

Before macro run:

After macro run:

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com