Deleting dataseries in a chart in Excel VBA 2016

问题:

I've got a dynamic chart, and I get into difficulties clearing the dataseries out of it.

From what I understand, the dataseries indices are cumulative. Meaning, if I copy the "chart 4" from another sheet with 12 dataseries cleanly made. The number of dataseries = 12 = ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection.count. And the indices for these series run from 1 to 12.

Now if I delete 1 series and a new one, the number of dataseries will continue to be 12, but the indices will now run from 1-11, and 13.

So when I'm trying to delete them by counting the number of series and deleting the series with indices 1: ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection.count It will fail if series have been removed and added.

So in order to overcome that problem, I tried the "for each.. in chart 4.. option:

For Each Series In ActiveSheet.ChartObjects("Chart 4")
'For Each FullSeriesCollection In ActiveSheet.ChartObjects("Chart 4")
    ActiveChart.FullSeriesCollection.Delete
Next

For that I get an error stating: "Object doesn't support this property or method"

So I looked the problem up here on stack overflow, and found due to the diligent procedure of indices of dataseries in graphs, the counter needs to go down:

Hence I copied and adjusted from VBA deleting chart series :

Dim iSrs As Long
With ActiveChart
    For iSrs = .SeriesCollection.count To 1 Step -1
        If InStr(LCase$(.SeriesCollection(iSrs).name), "series") > 0 Then
            .SeriesCollection(iSrs).Delete
        End If
    Next
End With
MsgBox (count_non_existant_series & " and the nr of series still present = " & ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection.count)

This does not delete all the data series, for afterwards it still shows:count = 27

I tried several other formulations with either the result of not deleting all, (sometimes with the "on error resume next" on, it would delete half of it, rounding down when odd) And the complete code is:

'select workbook, worksheet
Workbooks("N.xlsm").Worksheets("day_visual").Activate
Workbooks("N.xlsm").Worksheets("day_visual").range("A1").Select
'select chart
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Select
'remove all series(0 to xx?)
MsgBox (ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection.count)
'For Remove = 1 To ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection.count
'    'On Error Resume Next
'    ActiveChart.FullSeriesCollection(Remove).Select
'    Selection.Delete
'
'    'ActiveChart.FullSeriesCollection(Remove).Delete
'    'MsgBox ("hi")
'    count_non_existant_series = 1 + count_non_existant_series
'Next Remove

ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Select
'For x = Workbooks("N.xlsm").Worksheets("day_visual").ChartObjects("Chart 4").SeriesCollection.count To 2 Step -1
'For x = Workbooks("N.xlsm").Worksheets("day_visual").ChartObjects("Chart 4").FullSeriesCollection.count To 2 Step -1
'   ActiveSheet.ChartObjects("Chart 4").SeriesCollection(x).Delete
'Next x
Dim iSrs As Long
With ActiveChart
    For iSrs = .SeriesCollection.count To 1 Step -1
        If InStr(LCase$(.SeriesCollection(iSrs).name), "series") > 0 Then
            .SeriesCollection(iSrs).Delete
        End If
    Next
End With
'For Each Series In ActiveSheet.ChartObjects("Chart 4")
For Each FullSeriesCollection In ActiveSheet.ChartObjects("Chart 4")
    ActiveChart.FullSeriesCollection.Delete
Next


MsgBox (count_non_existant_series & " and the nr of series still present = " & ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection.count)

'With ActiveSheet.ChartObjects("Chart 4")
''Do While .SeriesCollection.count >= 1
'.SeriesCollection(.SeriesCollection.count).Delete
'Loop
'End With
Dim add_chartlabels As Long

My understanding of the exact nature of how the indices are stored by excel is lacking, causing me to attempt improper solutions. Can anyone either:

1. Tell me whether/how my understanding of the chart series indices is incorrect.
2. Explain why the code produces the runtime 483 error on the "for each" approach?
3. Explain why the manually iterative code does not remove all the series?
4. Show a functional code that removes any and all series from the chart whilst not deleting the chart itself?

Or if you have any other contributions that bring insight, you would make me very happy.

回答1:

Runtime 483 error on the For Each approach - since using this method means you are looping from the first to the last. When deleting objects you need to loop backwards. Therefore, for this purpose you need to use For iSrs = .SeriesCollection.count To 1 Step -1.

Try the code below, explanations inside the code (as comments):

Option Explicit

Sub DeleteChartSer()

Dim Sht As Worksheet
Dim ChtObj As ChartObject
Dim Ser As Series
Dim iSrs As Long

' set the worksheet object (this will work only if "Nutrition planner v42.xlsm" is open)
Set Sht = Workbooks("Nutrition planner v42.xlsm").Worksheets("day_vita_visual")

' set the ChartObject
Set ChtObj = Sht.ChartObjects("Chart 4")

MsgBox ChtObj.Chart.SeriesCollection.Count

With ChtObj.Chart ' <-- there's no need to select the Chart, use fullay qualified objects instead
    If .SeriesCollection.Count >= 0 Then
        For iSrs = .SeriesCollection.Count To 1 Step -1 ' allways loop backwards when deleting objects
            If LCase(.SeriesCollection(iSrs).Name) Like "*series*" Then
                .SeriesCollection(iSrs).Delete
            End If
        Next iSrs
    End If
End With

'MsgBox (count_non_existant_series & " and the nr of series still present = " & ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection.Count)

End Sub

---

Edit 1: if you want to remove all the Series, just comment one If, the one below, since here you check if the Series.Name contains the works "series":

If LCase(.SeriesCollection(iSrs).Name) Like "*series*" Then

So replace your last part of the code with:

With ChtObj.Chart ' <-- there's no need to select the Chart, use fullay qualified objects instead
    If .SeriesCollection.Count >= 0 Then
        For iSrs = .SeriesCollection.Count To 1 Step -1 ' allways loop backwards when deleting objects
            .SeriesCollection(iSrs).Delete
        Next iSrs
    End If
End With

回答2:

After delete if statement, it will work.

With ActiveChart
    If  .SeriesCollection.count >0 then
      For iSrs = .SeriesCollection.count To 1 Step -1
        'If InStr(LCase$(.SeriesCollection(iSrs).name), "series") > 0 Then
            .SeriesCollection(iSrs).Delete
        'End If
      Next
    end if
End With

回答3:

Do While ActiveChart.SeriesCollection.Count > 0
  ActiveChart.SeriesCollection(1).Delete
Loop