Loop through chart series names

[VegasNath]

Hi,
Is there a way to loop through all series names in a chart and delete those series where the name begins "series", in other words, all series that have not otherwise been re-named specifically?

[VegasNath]

Apologies, I should probably be a little more specific. I have various charts each with 12 series, however in some cases some of those series are completely nul. Each series is named as part of a routine, but the "nul" series (for some reason) do not get named but retain their original names and show in the legend. I am trying to avoid nul series from both the chart and the legend (without changing the source ranges). Hope this makes sense.

[HansV]

You have to loop backwards (see Looping backwards to delete items):

Code: Select all

Sub DeleteSeries()
  Dim cht As Chart
  Dim i As Long
  ' Modify as needed
  Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
  For i = cht.SeriesCollection.Count To 1 Step -1
    If cht.SeriesCollection(i).Name Like "Series*" Then
      cht.SeriesCollection(i).Delete
    End If
  Next i
End Sub

[VegasNath]

Hans, I get a 1004 "Unable to get the name property of the series class". When I look at the series tab of the source data dialogue, there are series10, series11 etc but the name field is blank. So maybe delete un-named series?

[HansV]

Does this work?

Code: Select all

Sub DeleteSeries()
  Dim cht As Chart
  Dim i As Long
  Dim strName As String
  ' Modify as needed
  Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
  For i = cht.SeriesCollection.Count To 1 Step -1
    strName = ""
    On Error Resume Next
    strName = cht.SeriesCollection(i).Name
    On Error GoTo 0
    If strName = "" Or strName Like "Series*" Then
      cht.SeriesCollection(i).Delete
    End If
  Next i
End Sub

[VegasNath]

no. Delete method of series class failed.

[HansV]

Could you post a sample workbook? I cannot reproduce your errors.

[VegasNath]

Sure. I fear that this may be getting a little out of control, I am fixing one thing and breaking another.

[HansV]

Which selections/actions in the userform cause the error? I enabled the delete code in your workbook and played around with it a bit but couldn't cause an error message to appear.

[VegasNath]

I put the delete series code at the bottom of the updateone macro, which executed ok but did not delete the unwanted series. When I commented out the 'on error resume next' from the top of that routine, then I get the error.

[HansV]

Of course you can't remove or comment out On Error Resume Next. I didn't put it there for nothing!

How do you create the "nameless" series? Whenever I manipulate a chart, the series are named co1, co2, ..., co12.

[VegasNath]

Companies 10, 11 & 12 do not contain all metrics, so if you select for example metric 9, I see companies 1 to 9 (that have detail) and series 10, 11, 12 that do not. Nothing is visible in the plot area for the 3 eroneous series, but they do appear in the legend.

Capture5.JPG

[HansV]

I get this:

x582.png

[VegasNath]

Hans, were you using 2007 or 2010 by any chance? Further testing has revealed that the problem for me arises in 2003 but not 2007.

[HansV]

I have tested it both in Excel 2003 SP3 and in Excel 2007 SP2.

[VegasNath]

It's definitely a bit odd. On 3 different machines with 2003, I get the same problem, yet 2 machines with 2007 and it works fine. Not to worry though, I found a solution by creating blank series in the database (using zeros) so that each series exists and can be named. They can then be turned off (deleted) with the checkbox.

Thanks for your time with this.

[rory]

Change the delete line from:

Code: Select all

      .SeriesCollection(i).Delete

to

Code: Select all

      .SeriesCollection(i).ChartType = xlColumnClustered
      .SeriesCollection(i).Delete