How to Delete only extra Legend Entries that are not needed in a stacked bar chart(Gantt) in Excel VBA?

Question

I found the question "Deleting legend entries with blank series name using VBA" here Very similar to the problem I'm Having but with a twist.

I have two buttons, "Load Chart" and "Clear Chart" like this

The chart is a stacked bar chart with multiple series each. I have some padding in which fill color and line border have been removed so that It looks like a gantt chart. It currently looks like this(Dummy)

Also the legend entry which is named padding and a few extra legend entries at the end have to be removed. I tried the following code which gets triggered at the end of load chart macro:

With ActiveChart
        For i = .Legend.LegendEntries.Count To UptoRequiredSeries + 1 Step -1
                    .Legend.LegendEntries(i).Delete
'            Debug.Print .Legend.LegendEntries(i - 1)
        Next i
End With

I dont want to use the IsFiltered property as suggested in the linked question. I literally want to remove the legend entries that I don't want. The problem gets compounded as I found that the chart retains number of legend entries after clearing the chart. So The next time I load the chart there will be problem with existing legend entries also getting removed.

Your help is solicited, thanks in advance.

EDIT1: I had this problem because there were more series than legends and that was because some series colors had to repeat. Like for ex: Series A followed by B and then Closed and then A Again. When a color needs to repeat I create a new series and set zeros for other cases of it in the data array. I use slicing operations to set the values array.

Answer 1

You need to do a little bookkeeping, to keep track of which legend entries you want to delete. Unfortunately the series and legend entries aren't related through the object model, a serious shortcoming.

Then you need to delete them from the last one (highest number) to the first.

You don't go into the criteria for legend entry removal, but suppose you know you want to delete the last two and the first one, "padding". You would do something like this:

Dim iSrs As Long
For iSrs = ActiveChart.SeriesCollection.Count To ActiveChart.SeriesCollection.Count - 1 Step -1
    ActiveChart.Legend.LegendEntries(iSrs).Delete
Next
ActiveChart.Legend.LegendEntries(1).Delete

If it's more complicated, you need to just set up perhaps an array (1 to .SeriesCollection.Count) and enter in this array True or False whether to delete the corresponding legend entry, then count down from the last to the first, and if the corresponding array element is True, delete the legend entry.

If you've deleted some entries and need to get them back, delete the legend and restore it. The restored legend will contain all legend entries, and you can start over.