Deleting SeriesCollections in charts

[MAWII]

I have a spreadsheet with 6 columns and 6 rows. The first column is the
x-value for all series. The other 5 columns are the 5 series of y-values.
Each column has 5 possible user inputs (all data is entered via userform and
the 6th value is automatically calculated from that data). If there are no
user inputs for the column, I don't want the series to show up on the chart.
I've tried something like the following, but can't get it to work. If I just
countblank < 5 and delete the series if it's false, it deletes that series
number and renumbers the remaining series (i.e. delete series 3 and series 4
becomes 3 and so on...). Any thoughts? Thanks!

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets(strShotID).Range("A1:F7"),
PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=strShotID
ActiveChart.PlotVisibleOnly = True

If Application.WorksheetFunction.CountBlank(Range("B3:B7")) < 5 Then
ActiveChart.SeriesCollection(1).Values =
Sheets(strShotID).Range("B2:B7")
ActiveChart.SeriesCollection(1).XValues =
Sheets(strShotID).Range("A2:A7")
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, _
Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
True).Select
End If

If Application.WorksheetFunction.CountBlank(Range("C3:C7")) < 5 Then
If ActiveChart.SeriesCollection(1).Values =
Sheets(strShotID).Range("B2:B7") Then
ActiveChart.SeriesCollection(2).Values =
Sheets(strShotID).Range("C2:C7")
ActiveChart.SeriesCollection(2).XValues =
Sheets(strShotID).Range("A2:A7")
ActiveChart.SeriesCollection(2).Trendlines.Add(Type:=xlLinear, _
Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
True).Select
Else
ActiveChart.SeriesCollection(1).Values =
Sheets(strShotID).Range("C2:C7")
ActiveChart.SeriesCollection(1).XValues =
Sheets(strShotID).Range("A2:A7")
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, _
Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
True).Select
End If
End If

 

[Jon Peltier]

If I just

countblank < 5 and delete the series if it's false, it deletes that series
number and renumbers the remaining series (i.e. delete series 3 and series 4
becomes 3 and so on...). Any thoughts?

If you have five series, and delete one, then you have four. They are now labeled 1
through 4, so any index numbers after the one you delete will be reduced by one.
This is expected behavior.

Try checking the series starting with SeriesCollection(5) and count down to 1.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[MAWII]

Thanks! I never thought to work backwards!
-Mark