VBA code to automatically add multiple series to a scatter plot IF(ISNUMBER=true)

[MylesMc]

I need to dynamically add series' to a smooth line scatter plot chart if there is any number in a cell in column A. If $A$1 has a number (ISNUMBER function should work fine here I believe), then plot series such as: SERIES(sheet1!$A$1,sheet1!B$1:$B$37,sheet1!$C$1:$C$37,1).

I need the code to repeat over the same size of ranges that will continue all the way down to row 5476. This means there could potentially be 5476/37 = 148 Series in my plot.

Each Series will be the same size of 37 rows with Column A having the same value over those 37 rows for a series. Column B will be the X-axis and Column C will be the Y-axis. When Column A no longer contains a number the code should stop and not display blank series in the legend of the plot. The length of this table changes depending on the number of different inflows I have in Column A, and therefore the number of different series I have in my plot, which may vary from 1 to 148 inflows/series.

The attached table is an exerpt of the data being plotted. In this excerpt there would be two series plotted named 60 and 80.

Thanks for any help,
Myles Mc
Book2.xlsx

[p45cal]

First, for others, this has been cross-posted at ExcelForum.com.
MylesMc, have you cross posted elsewhere too? If so, could you say where please?

Second, the problem:
Try the following in your sample workbook:

Sub blah()
Range("A1").Select  'in case the chart is selected.
Range("A1").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
With ActiveSheet.ChartObjects("Chart 1").Chart
'delete all existing series first:
  For i = 1 To .SeriesCollection.Count
    .SeriesCollection(1).Delete
  Next i
  For Each ar In Intersect(ActiveSheet.UsedRange, Columns("B:B")).Offset(1).SpecialCells(xlCellTypeConstants, 23).Areas
    '  ar.Offset(, -1).Resize(, 3).Select
    '  ar.Cells(1).Offset(, -1).Select
    '  ar.Columns(1).Select
    '  ar.Columns(2).Select
    With .SeriesCollection.NewSeries
      .XValues = ar.Columns(1)
      .Values = ar.Columns(2)
      .Name = ar.Cells(1).Offset(, -1)
      With .Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
        .ColorIndex = 1
      End With
      .MarkerStyle = xlNone
      .Smooth = True
    End With
  Next ar
End With
Range("A1").RemoveSubtotal
End Sub

This was developed in Excel 2003.

[MylesMc]

Hey p45cal,

Thanks for your reply. The code works great. Thank you very much!

This question was cross posted to ExcelForum.com and MrExcel.com.

Thanks Again,
Myles Mc