Solved: Another Dynamic Range Question

[Sock]

I want to fill an XY scatterplot with multiple series. These series' data are all placed in a worksheet. The X values are located in the 'E' column and the Y values are located in the 'I' column. Both start at row 5 (ie. the X values run from E5:something and the Y values run from I5:something).

The thing is that I have multiple series that are in these columns.

Let's say a user has 4 variables, with 5 points. This means I would have four series, each with 5 datapoints in X and Y. This would mean the first series would have X values from E5:I9, E10:I14, E15:I19, E20:I24, and similar for the Y values in the I column. As you can see, the series number would change as would the total datapoints in each series.

I am completely stumped as to how I would accomplish this. I know I could loop to add the series, but as for how to get the range to select the range based on the number of datapoints escapes me.

Thank you all so much for everything! I've learned so much thanks to this website!! :)

[p45cal]

E5:I9, E10:I14, E15:I19, E20:I24So this (contiguous) range you want plotting as 4 separate lines on an xy scatter graph? How would a human, on seeing either the values therein, or something in another column(s), know where to split that range for the 4 lines?

If you could give us a sample sheet to experiment with…

[Sock]

So this (contiguous) range you want plotting as 4 separate lines on an xy scatter graph? How would a human, on seeing either the values therein, or something in another column(s), know where to split that range for the 4 lines?

If you could give us a sample sheet to experiment with…

Thank you for the quick reply!

Yes, it's a contiguous range with four separate lines in this example. There could be times where there are 20 lines or times where there are only 2.

There is a column that lists the points in each line (in this case, with 5 points in each series, it goes from 1 to 5). So a human would be able to figure it out when it rolls over to 1 which would indicate that that's the start of the next series.

I hope that makes sense. Again, thank you for your help. I'll try to attach a quick whipped-up example

[p45cal]

In the attached is a button added to each of the three sheets which when clicked runs the macro blah. There are notes on the first sheet and comments in the code. The macro runs on the active sheet.

The code is already in the attached but for others, the code:Sub blah()
If ActiveSheet.ChartObjects.Count = 0 Then 'add a chart if there isn't one:
With ActiveSheet.Shapes.AddChart(xlXYScatterLinesNoMarkers, Range("L4").Left, Range("L4").Top, 360, 316)
.Chart.Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
End With
End If
Set myChart = ActiveSheet.ChartObjects(1).Chart 'act on the first chart on the sheet
ClearPreviousPlots myChart 'remove all series from the chart
Set startblock = Range("B4") 'the start of the first block
For Each cll In Range(Range("B4"), Range("B4").End(xlDown)).Cells 'run down the cells in column B:
'cll.Select 'debug line to track where we are
Set endblock = cll 'this gets updated each time round the loop
If cll.Value > cll.Offset(1).Value Then
'cll is the end of a block so plot rows startblock to endblock
'Range(startblock, endblock).Select 'debug line to ensure we have the right range
Set BlockToPlot = Range(startblock, endblock)
AddASeries myChart, BlockToPlot.Cells(1).Offset(, -1), BlockToPlot.Offset(, 3), BlockToPlot.Offset(, 7)
'now a block has been plotted set new startblock
Set startblock = cll.Offset(1)
End If
Next cll
End Sub

Sub AddASeries(TheChart, nme, Xs, Ys)
With TheChart.SeriesCollection.NewSeries
.Name = nme
.XValues = Xs
.Values = Ys
End With
End Sub

Sub ClearPreviousPlots(TheChart)
With TheChart.SeriesCollection
For i = 1 To .Count
.Item(1).Delete
Next i
End With
End Sub

[Sock]

In the attached is a button added to each of the three sheets which when clicked runs the macro blah. There are notes on the first sheet and comments in the code. The macro runs on the active sheet.

It's so beautiful, I could cry.

This is awesome. Easily adapted it to work within my code. Very very clever work, a million thank you's to you, good sir!

:friends: :friends: :friends: