Add multiple data series to excel chart with VBA

Question

Assuming I have my data in rows and I want to create a chart (x,y scatter) from it.

date    1.1. 1.2. 1.3. 1.4 1.5
set1    12  36  12  23  33
set2    44  22  11  1   13
set3    54  5   56  56  34
set4    1   2   6   12  33

how can I add set1,set2 and set4 but not set3 to the chart without having to select them individually one after another?

For the x-range (date) I can do

Dim xrange As Range
Set xrange = Application.InputBox("select x-range", "Get Range", Type:=8)

But doing the same for the data sets (selection of multiple rows) won't work because each series in a scatter plot requires a individual range

   ActiveChart.SeriesCollection(1).XValues = xrange
   ActiveChart.SeriesCollection(1).Values = "=Sheet1!$4:$4"

but I have a selection of multiple datasets. I would need a way to split the ranges from my input box selection(s) and to write them to unique variables. Then I would add a new series for each xrange + variable pair with the above method.

Is there a wayto do this?

A potentail answer does not need to stick to what I posted above, any suggestions are welcome.

Answer 1

Set the source data range. Excel will automatically create the series for you. In the example below I have column A as X, column B as Y1 and column D as Y2:

ActiveChart.SetSourceData Source:=Range("A1:B4,D1:D4")