CODEWITHSUNDEEP
excelvbacharts
a.t.
a.t.

Reputation: 2779

Deleting dataseries in a chart

I have a dynamic chart, and I get into difficulties clearing the dataseries.

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 one series and add one, the number of dataseries will continue to be 12, but the indices will now run from 1-11, and 13.

So when I try 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.

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

I get an error stating:

"Object doesn't support this property or method"

I looked the problem up here on stackoverflow, and found the counter needs to go down:

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 how the indices are stored by Excel is lacking, causing me to attempt improper solutions.

  1. Is my understanding of the chart series indices incorrect?
  2. Why does the "for each" approach produce the runtime 483 error?
  3. Why doesn't the manually iterative code remove all the series?
  4. How can I remove all series from the chart whilst not deleting the chart itself?

Upvotes: 2

Views: 2706

Answers (3)

Jon Peltier
Jon Peltier

Reputation: 6073

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

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

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

Upvotes: 2

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 2

Related Questions