Help setting chart series values with non-continuous range

[Guest]

I am trying to create a chart with multiple series and each series having
values from non-continuous ranges. Can someone help by explaining why this
doesn't work or what I should do instead.

Charts.Add
ActiveChart.ChartType = xlLineMarkers
With ActiveChart.SeriesCollection.NewSeries
.Name = Worksheets("Review").Cells(vX, 2)
.Values = Union(Range(Worksheets("Review").Cells(11, 7),
Worksheets("Review").Cells(11, 24)), Range(Worksheets("Review").Cells(50,
25), Worksheets("Review").Cells(50, 61)))
End With

I found the Uniton on this forum and it works for
ActiveChart.SetSourceData Source:=
But I need to do multiple series.

Thanks,
John

 

[Guest]

Have you thought about defining a non contiguous named range and then
applying that to the series?

 

[Guest]

I can't say this is the best way, but it worked for me:

Sub AAA()
Dim vx as Long, sh as Worksheet
vx = 1
Charts.Add
ActiveChart.ChartType = xlLineMarkers
Set sh = Worksheets("Review")
With ActiveChart.SeriesCollection.NewSeries
.Name = sh.Cells(vx, 2)
.Formula = "=SERIES(,,(" & sh.Range( _
sh.Cells(11, 7), _
sh.Cells(11, 24)) _
.Address(1, 1, xlA1, True) & "," & _
sh.Range(sh.Cells(50, 25), _
sh.Cells(50, 61)) _
.Address(1, 1, xlA1, True) & "),1)"
End With
End Sub

 

[Jon Peltier]

In general:

With Worksheets("Review")
Set rngCht = Union(.Range(.Cells(11, 7), .Cells(11, 24)),
..Range(.Cells(50, 25), .Cells(50, 61)))
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = Worksheets("Review").Cells(vX, 2)
.Values = rngCht
End With

but Excel doesn't let you use a range that isn't a single row or a single
column for values, and your values are in rows 11 and 50. I generally prefer
to work with .Values and .XValues than with the series .Formula, but here
it's unavoidable. Use Tom's procedure.

- Jon

 

[Guest]

Thanks, this did it for me! Although, I had to move the .Name = declaration
to after .Formula = . For some reason it would take the name assigment after
but not before.. Either way that got it done, thanks. I may also try the
Set rngCht = suggested by Jon to set the value instead of the formula.

Always greate help here!