删除图表中的数据序列
删除图表中的数据序列
提问于 2017-07-16 20:03:01
我有一个动态图表，我在清理数据集时遇到了困难。
据我所知，数据集索引是累积的。意思是，如果我复制“图表4”从另一个工作表与12个干净的数据集。数据序列的数量= 12 =ActiveSheet.ChartObjects(“图4").Chart.SeriesCollection.count和这些系列的索引从1到12。
现在，如果我删除一个系列并添加一个系列，那么数据集的数量将继续是12个，但是索引现在将从1-11和13开始运行。
因此，当我试图通过计数序列数和删除索引1: ActiveSheet.ChartObjects(“图表4").Chart.SeriesCollection.count的系列来删除它们时，如果删除并添加了序列，则会失败。
为了克服这个问题，我尝试了“图4中的每个.选项”：
For Each Series In ActiveSheet.ChartObjects("Chart 4")
'For Each FullSeriesCollection In ActiveSheet.ChartObjects("Chart 4")
ActiveChart.FullSeriesCollection.Delete
Next我收到一个错误声明：
“对象不支持此属性或方法”
我在这里查看了堆栈溢出的问题，发现柜台需要下降：
我复制并调整了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。
我尝试了其他几种公式，结果要么没有全部删除，要么(有时使用“启动错误恢复下一步”，它会删除其中的一半，在奇数时取四舍五入)，完整的代码是：
'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如何存储索引的理解，这导致我尝试了不适当的解决方案。
- 我对图表系列指数的理解是否不正确？
- 为什么“为每个”方法会产生运行时483错误？
- 为什么手动迭代代码不删除所有系列？
- 如何在不删除图表本身的同时从图表中删除所有系列？
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-07-17 03:00:23
运行时For Each方法上的483错误-因为使用此方法意味着您将从第一个循环到最后一个循环。删除对象时，需要向后循环。因此，为此您需要使用For iSrs = .SeriesCollection.count To 1 Step -1。
尝试下面的代码，代码内部的解释(作为注释)：
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是否包含工程“系列”：
If LCase(.SeriesCollection(iSrs).Name) Like "*series*" Then因此，将代码的最后一部分替换为：
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 WithStack Overflow用户
发布于 2017-07-16 23:25:27
在删除if语句之后，它将工作。
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 WithStack Overflow用户
发布于 2019-05-22 15:37:57
Do While ActiveChart.SeriesCollection.Count > 0
ActiveChart.SeriesCollection(1).Delete
Loop页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45132822
复制相关文章
相似问题
腾讯云开发者
