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.

You have to use .SeriesCollection.NewSeries. See my reply in stackoverflow.com/questions/9778827/…. — Siddharth Rout
– Siddharth Rout, Commented Apr 3, 2012 at 23:03

bouvierr · Accepted Answer · 2012-04-03 23:56:22Z

5

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")

answered Apr 3, 2012 at 23:56

bouvierr

3,8513 gold badges30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Peltier Over a year ago

Delete the label ("Date" in this case) in the cell atop the X values, to help Excel recognize that they are X values and not another set of Y values. This might be unnecessary if Excel notices date formats in the first column, but it's better to be sure.

Collectives™ on Stack Overflow

Add multiple data series to excel chart with VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related