How delete a series from an Excel chart using VBA

Question

I'm trying to delete the empty series from a chart in Excel 2003 using VBA. I've seen that others have had this issue in the past and I have tried all methods mentioned in their posts but have been unable to find anything that works consistently.

The chart has 14 series in it and anywhere between 3 or 9 of them can be empty. The empty ones are always between Series 4 - 12.

I've tried a few variations of code but this is primarily it:

Sheets("chart-1").Select
ActiveChart.PlotArea.Select
For i = 12 To 4 Step -1
    Dim theSeries As Series
    MsgBox (ActiveChart.SeriesCollection(i).Name)
    Set theSeries = ActiveChart.SeriesCollection(i)
    MsgBox (theSeries.Name)
    theSeries.Delete
Next

I can run it successfully once for a chart, but all subsequent cycles fail with a Unable to get the Name property of the Series class error. It fails on the call to .Name.

I been able to get it work by inserting integers directly, but it will only run once for all integers except 1. It run it multiple times for Series(1).

For instance if I simply call: ActiveChart.SeriesCollection(1).Delete, then the series is deleted, but if I then run it with another integer (4, 9, 12) it won't run. It will work again for 1, but only 1. It will also work once with other integers (say 4), but all subsequent calls will fail even if I change the integer to 1 or keep it as 4, or change it to some other number.

The behaviour is really quite strange.

Any ideas would be greatly appreciated. I can't simply call ActiveChart.SeriesCollection(1).Delete repeatedly because the first 3 series are always non-empty.

Thanks.

** Update **

I just ran a test manually executing the following:

Sheets("ch-v2-12mth").Select
ActiveChart.PlotArea.Select
MsgBox (ActiveChart.SeriesCollection(1).Name)

I cycled through the SeriesCollection trying the numbers 1 - 16 (there are only 14 Series in the chart) to see the result. 1 - 3 worked fine 4 - 13 errored with Unable to get the Name property of the Series class 14 worked fine 15 - 16 errored with Method 'SeriesCollection' of object '_Chart' failed <- not surprising given the number of series in the chart.

This type of behaviour makes me think that there is a bug with Excel. Any other ideas?

Answer 1

There are bugs in Excel when you delete all the series from a chart. My workaround is to always leave at least one series (even if it has no data in it) in the chart. That seems to work for me.

Just thought of another thing. When you delete a series, the indexes of all the remaining series get reduced by one, so you can't delete them by looping from 1 to the number of series. What you can do instead is have a do loop that deletes them until the SeriesCollection.Count = 0 (or 1, see my comments earlier). Or a for loop that iterates backwards and always deletes the last series (i.e. SeriesCollection(SeriesCollection.Count).Delete