Remove a particular legend from a Chart using VBA

**Kindred Spirit** · 12-20-2018, 06:17 PM

Hi thanks a lot for this. Excel does not maintain the same ordering for series and legend, if the chart has got a mixed type of series e.g. line, column etc and also primary and secondary vertical axis. So, using the color property to delete the legend entry of a particular series is a very good idea as long as colors do not repeat. However, I found the ForeColor property was not working (using Excel 2013). So, I modified the code in order to use Border color instead and it works.

Code:

Option Explicit

Function delete_legend_entry(ByRef chartObject As chartObject, ByVal seriesName As String)
    
' Got the idea from http://www.excelfox.com/forum/showthread.php/2075-Remove-a-particular-legend-from-a-Chart-using-VBA
' But the reference used Interior colour which is not unique for all series, so I used border colour
    
    Dim chtChart        As chart
    Dim srsSeries       As Series
    Dim lngSrsColor     As Long
    Dim lngLoop         As Long
    
    Set chtChart = chartObject.chart
    
    lngColor = chtChart.PlotArea.Format.Fill.ForeColor.RGB
    
    For Each srsSeries In chtChart.SeriesCollection
        If LCase(srsSeries.Name) = LCase(seriesName) Then
            lngSrsColor = srsSeries.Border.Color
            Exit For
        End If
    Next
    
    For lngLoop = chtChart.Legend.LegendEntries.Count To 1 Step -1
        If chtChart.Legend.LegendEntries(lngLoop).LegendKey.Border.Color = lngSrsColor Then
            chtChart.Legend.LegendEntries(lngLoop).Select
            chtChart.Legend.LegendEntries(lngLoop).Delete
            Exit For
        End If
    Next
    
End Function