Re: plot non.cintiguos range

The range you describe consists of adjoining cells, but the union makes
them discontiguous in a way that Excel cannot deal with.

You could do this a different way:

Sub ChartIt()
Dim srs As Series
Set srs = Charts("chart2").SeriesCollection.NewSeries
With srs
.Values = Worksheets("sheet1").Range(Cells(2, 2), Cells(4, 2))
.XValues = Worksheets("sheet1").Range(Cells(2, 1), Cells(4, 1))
.Name = Worksheets("sheet1").Cells(1, 1)
End With
Set srs = Charts("chart2").SeriesCollection.NewSeries
With srs
.Values = Worksheets("sheet1").Range(Cells(6, 2), Cells(8, 2))
.XValues = Worksheets("sheet1").Range(Cells(6, 1), Cells(8, 1))
.Name = Worksheets("sheet1").Cells(2, 1)
End With
End Sub

NewSeries in conjunction with Values and XValues provides more control
over what the code does to your chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

ojv wrote:
> Hi,
> I'm trying to add new data to an existing chart via
> VBA. The new series contain non contiguos ranges which I
> join thriugh union. Using .seriescollection.add I get the
> message that this is not appliccable for multiple
> selections - code below:
>
> Worksheets("sheet1").Activate
> Set legrng = Worksheets("sheet1").Range(Cells(1, 2), Cells
> (2, 2))
> Set x1rng = Worksheets("sheet1").Range(Cells(2, 1), Cells
> (4, 1))
> Set y1rng = Worksheets("sheet1").Range(Cells(2, 2), Cells
> (4, 2))
> Set x2rng = Worksheets("sheet1").Range(Cells(6, 1), Cells
> (8, 1))
> Set y2rng = Worksheets("sheet1").Range(Cells(6, 2), Cells
> (8, 2))
>
> Set pltrng = Union(legrng, x1rng, y1rng, x2rng,
> y2rng)
> Charts("chart2").Activate
> Charts("chart2").SeriesCollection.Add
> Source:=pltrng, Rowcol:=xlColumns, SeriesLabels:=True, _
> CategoryLabels:=True, Replace:=False
>
> Is there any way around this?
>
> Any help appreciated.
>
> ojv

Thx a lot )
>-----Original Message-----
>The range you describe consists of adjoining cells, but
the union makes
>them discontiguous in a way that Excel cannot deal with.
>
>You could do this a different way:
>
>Sub ChartIt()
>Dim srs As Series
>Set srs = Charts("chart2").SeriesCollection.NewSeries
>With srs
> .Values = Worksheets("sheet1").Range(Cells(2, 2),
Cells(4, 2))
> .XValues = Worksheets("sheet1").Range(Cells(2, 1),
Cells(4, 1))
> .Name = Worksheets("sheet1").Cells(1, 1)
>End With
>Set srs = Charts("chart2").SeriesCollection.NewSeries
>With srs
> .Values = Worksheets("sheet1").Range(Cells(6, 2),
Cells(8, 2))
> .XValues = Worksheets("sheet1").Range(Cells(6, 1),
Cells(8, 1))
> .Name = Worksheets("sheet1").Cells(2, 1)
>End With
>End Sub
>
>NewSeries in conjunction with Values and XValues provides
more control
>over what the code does to your chart.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>http://www.geocities.com/jonpeltier/Excel/index.html
>_______
>
>ojv wrote:
>> Hi,
>> I'm trying to add new data to an existing chart via
>> VBA. The new series contain non contiguos ranges which
I
>> join thriugh union. Using .seriescollection.add I get
the
>> message that this is not appliccable for multiple
>> selections - code below:
>>
>> Worksheets("sheet1").Activate
>> Set legrng = Worksheets("sheet1").Range(Cells(1, 2),
Cells
>> (2, 2))
>> Set x1rng = Worksheets("sheet1").Range(Cells(2, 1),
Cells
>> (4, 1))
>> Set y1rng = Worksheets("sheet1").Range(Cells(2, 2),
Cells
>> (4, 2))
>> Set x2rng = Worksheets("sheet1").Range(Cells(6, 1),
Cells
>> (8, 1))
>> Set y2rng = Worksheets("sheet1").Range(Cells(6, 2),
Cells
>> (8, 2))
>>
>> Set pltrng = Union(legrng, x1rng, y1rng, x2rng,
>> y2rng)
>> Charts("chart2").Activate
>> Charts("chart2").SeriesCollection.Add
>> Source:=pltrng, Rowcol:=xlColumns, SeriesLabels:=True, _
>> CategoryLabels:=True, Replace:=False
>>
>> Is there any way around this?
>>
>> Any help appreciated.
>>
>> ojv
>
>.
>