Solved: VBA how to Delete Empty / Valueless Series from Chart

[nb-]

I have a line chart with a number of series in it, some of which are have no data in them. They show up in the ledged, and are counted by .SeriesCollection.Count but for everything else they cause problems.

The problem is that these series seem to be empty/valueless, I have number of charts with a lot of these problem series in them, that I want to go through with VBA and delete. However .SeriesCollection(i).delete throws an error like the series is not there...

The following code is a simplified example to go with the simplified file attached. It throws an error on all the series I want to delete (red in the attached file), how can I delete these series using vba? :think:

Ideas / thoughts appreciated.




Option Explicit

Public Sub test()

Dim i As Integer

With ActiveChart
For i = .SeriesCollection.Count To 1 Step -1
Debug.Print i & " : " & .SeriesCollection(i).Name
.SeriesCollection(i).Delete
Next i
End With

End Sub

[Andy Pope]

Some chart types cause an error when they have no data in the cells they reference. In you case you can change the chart type of the series in order to delete it.



Public Sub test()

Dim i As Integer

On Error GoTo ErrDelete

With ActiveChart
For i = .SeriesCollection.Count To 1 Step -1
Debug.Print i & " : " & .SeriesCollection(i).Name
.SeriesCollection(i).Delete
Next i
End With
Exit Sub

ErrDelete:
ActiveChart.SeriesCollection(i).ChartType = xlColumnClustered
Resume

End Sub

[nb-]

Andy, thanks very much, just the ticket.:)

I'd not realised line charts misbehaved like that.

[flea333]

What if you wanted to delete the series altogether?