删除图表中的数据系列 [英] Deleting dataseries in a chart

问题描述

我有一个动态图表，在清除数据序列时遇到困难。

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

据我了解，数据序列索引是累积的。意思是，如果我从另一个工作表干净地复制了12个数据系列的图表4中进行复制。数据序列的数量= 12 = ActiveSheet.ChartObjects（图表4）。Chart.SeriesCollection.count以及这些系列的索引从1到12。

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如果我删除一个序列并添加一个序列，则数据序列的数量将继续为12，但索引现在将以1-11和13进行运行。

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.

因此，当我试图通过计算序列号并删除索引为1的序列来删除它们：ActiveSheet.ChartObjects（ Chart 4）。Chart.SeriesCollection.count如果删除并添加了序列，它将失败。

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.

为克服该问题，我尝试了对于每个..在图4 ..选项：

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

我收到一个错误提示：

对象不支持此属性或方法

"Object doesn't support this property or method"

我在stackoverflow上查找问题，发现计数器需要下降：

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

我复制并根据 VBA删除图表系列进行了调整：

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)

此操作不会删除所有数据系列，之后它仍然显示：count = 27。

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

我缺乏对Excel如何存储索引的理解，导致我尝试使用不合适的解决方案。

My understanding how the indices are stored by Excel is lacking, causing me to attempt improper solutions.

1. 我对图表系列指数的理解不正确吗？


2. 为什么 for each方法会产生运行时483错误？


3. 为什么手动迭代代码不能删除所有系列？


4. 如何在不删除
   图表本身的情况下从图表中删除所有系列？

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?

推荐答案

For Each 方法的运行时483错误-因为使用此方法意味着您从第一个循环到最后一个。删除对象时，您需要向后循环。因此，为此，您需要使用 For iSrs = .SeriesCollection.count到1步骤-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

---

编辑1 ：如果要删除所有 Series ，只需添加注释一个 If ，下面的一个，因为在这里检查 Series.Name 是否包含作品 series：

---

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

这篇关于删除图表中的数据系列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！