# Selecting Several Chart Series

#### iepritchard

##### Board Regular
Hi All,

I have code in order to automatically make a chart, but I need a slightly more complicated method of choosing the series to be charted.

I need to plot several series on the same graph, but the number of observations in each series depends on options chosen beforehand.
For example, take the data

X Y
A 1 12
A 2 11
A 3 17
A 4 20
B 2 11
B 5 12
B 6 13
C 1 10
C 8 15

I need to plot the separate series for rows with A, B, and C so they can be highlighted in different colours.

Another problem is that the number of rows for each letter eg. A, isn't fixed, so to select the series I need to search all rows for "A" and then select data for the series.

There also isn't a fixed number of series, the number ranges from 2 to 30 series, is it possible to accomodate this..??

Once again I just need the code to select the series with!

Any suggestions would be most gratefully recieved,

Thanks,
Ian

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi Ian

With the layout I post, try:

Code:
``````Sub ChartMultSeries()
Dim wsh As Worksheet, chtO As ChartObject
Dim serColl As SeriesCollection, rXValues As Range
Dim lRowS As Long, lRowE As Long

lRowE = 2 ' start in row 2
Set wsh = Worksheets("Sheet1") ' where the source table is and where the chart is created

With wsh
' Create the chart
Set chtO = .ChartObjects.Add(100, 100, 400, 200)
chtO.Name = "MyChart"
With chtO.Chart
.ChartType = xlXYScatterLines
Set serColl = .SeriesCollection
End With

Do While .Range("A" & lRowE).Value <> ""
lRowS = lRowE
Do While .Range("A" & lRowE + 1) = .Range("A" & lRowS)
lRowE = lRowE + 1
Loop
Set rXValues = .Range(.Range("B" & lRowS), .Range("B" & lRowE))
With serColl.NewSeries
.XValues = rXValues
.Values = rXValues.Offset(, 1)
.Name = rXValues(1).Offset(, -1)
End With
lRowE = lRowE + 1
Loop
End With
End Sub``````

Hi,

Thank you, that works really well! But can I please ask for one modification...? I would like to have one graph with fixed axes, titles, formatting etc, and then change the data in the series on that graph using the second part of your code. I assume you need to select the chart and then add/modify series, but i can't seem to get it to work..?!
Can you help me with this please?

Cheers,
Ian

Hi

Do you mean

- deleting all the existing series in your chart and add these new ones?
- or leaving the existing series and add these new ones?

It would be deleting all existing series and adding new ones. The data would still be in the same cells as before, they would just have different values.

Thanks,
Ian

OK. Adapt the name of the chart object (I used "MyChart"). With the same layout, try:

Code:
``````Sub ChartMultSeries()
Dim wsh As Worksheet, chtO As ChartObject
Dim serColl As SeriesCollection, ser As Series, rXValues As Range
Dim lRowS As Long, lRowE As Long, lSer As Long

lRowE = 2 ' start in row 2
Set wsh = Worksheets("Sheet1") ' where the source table is and where the chart is created

With wsh
' Get the .SeriesCollection of the chart
Set serColl = .ChartObjects("MyChart").Chart.SeriesCollection

' delete the existing series
For lSer = serColl.Count To 1 Step -1
serColl(lSer).Delete
Next lSer

Do While .Range("A" & lRowE).Value <> ""
lRowS = lRowE
Do While .Range("A" & lRowE + 1) = .Range("A" & lRowS)
lRowE = lRowE + 1
Loop
Set rXValues = .Range(.Range("B" & lRowS), .Range("B" & lRowE))
With serColl.NewSeries
.XValues = rXValues
.Values = rXValues.Offset(, 1)
.Name = rXValues(1).Offset(, -1)
End With
lRowE = lRowE + 1
Loop
End With
End Sub``````

Thanks again, I imagine i'm becoming a bit of a pain now, but the chart is on its own sheet "Ad_Chart", a different sheet from the dataset, how do I deal with this?

Thanks,
Ian

It's not clear if:

1 - the data is in a worksheet and the chart is embedded in another worksheet

2 - the chart is in a chart sheet, instead of embedded in a worksheet

Replace

Code:
``    Set serColl = .ChartObjects("MyChart").Chart.SeriesCollection``

for case 1 with:

Code:
``    Set serColl = Worksheets("Sheet2").ChartObjects("MyChart").Chart.SeriesCollection``

for case 2 with:

Code:
``    Set serColl = Charts("MyChart").SeriesCollection``

Thanks, that works well!

Replies
8
Views
204
Replies
0
Views
83
Replies
4
Views
279
Replies
1
Views
196
Replies
0
Views
323

### Forum statistics

1,207,198
Messages
6,077,018
Members
446,250
Latest member
Dontcomehereoften

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back