I currently have a chart series that ranges from 0 to 100 (thanks to Jon
Peltier) to reflect years. I can now add another series of data on top of
that. But what I was wondering is how can I change my series so that I can
show only the data for say year 20 to 40.



I have tried changing 'a' to 20 and 'c' to 40 and it retrieves the correct
data but puts the data over year 0 to 19.



Can someone please tell me what I am missing?



Cheers



David

---------------------------

Charts.Add

ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

ActiveChart.Location Whe=xlLocationAsObject, Name:="Spread Data"

With ActiveChart

.HasTitle = False

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False



End With



With ActiveChart.Axes(xlCategory)

.MinimumScale = 0

.MaximumScale = 100

.MajorUnit = 10

End With



With ActiveChart.Axes(xlValue)

.MinimumScale = 0

End With

'add new data then delete legend

a = 1

r = 6

With Worksheets("Spread Data")

Set Title_Names1 = .Cells(r, 1)

End With



With ActiveChart

.SeriesCollection.NewSeries

.SeriesCollection(2).Name = Title_Names1

End With



'loop through data and make it update

For c = 1 To 100

With Worksheets("Spread Data")

Set Area_rng1 = Range(.Cells(r, 1 + a), .Cells(r, 2 + c))

End With



With ActiveChart

.SeriesCollection(2).values = Area_rng1

End With



Next c

David,

If Tools | Options | Chart - "Not plotted ( leave gaps)" is checked then
1. you can change your data for the non-plotted periods to zero or
2. switch to an alternate data source that has zeros for the non-plotted periods.

Regards,
Jim Cone
San Francisco, CA

"David Adamson" wrote in message ...
I currently have a chart series that ranges from 0 to 100 (thanks to Jon
Peltier) to reflect years. I can now add another series of data on top of
that. But what I was wondering is how can I change my series so that I can
show only the data for say year 20 to 40.

I have tried changing 'a' to 20 and 'c' to 40 and it retrieves the correct
data but puts the data over year 0 to 19.
Can someone please tell me what I am missing?
Cheers
David

Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.Location Whe=xlLocationAsObject, Name:="Spread Data"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 100
.MajorUnit = 10
End With

With ActiveChart.Axes(xlValue)
.MinimumScale = 0
End With
'add new data then delete legend
a = 1
r = 6
With Worksheets("Spread Data")
Set Title_Names1 = .Cells(r, 1)
End With
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(2).Name = Title_Names1
End With
'loop through data and make it update
For c = 1 To 100
With Worksheets("Spread Data")
Set Area_rng1 = Range(.Cells(r, 1 + a), .Cells(r, 2 + c))
End With
With ActiveChart
.SeriesCollection(2).values = Area_rng1
End With
Next c

Thanks for that Jim,

I'll give it a go when I get back into the office.

I'll juts have to find a way to trigger it via code.

If not I'll use the 'union' function and insert dummy variables.



"Jim Cone" wrote in message
...
David,

If Tools | Options | Chart - "Not plotted ( leave gaps)" is checked
then
1. you can change your data for the non-plotted periods to zero or
2. switch to an alternate data source that has zeros for the non-plotted
periods.

Regards,
Jim Cone
San Francisco, CA

David,

I said use "zeros", I should have said use "blank cells".

Regards,
Jim Cone
San Francisco, CA

"Geeves" wrote in message u...
Thanks for that Jim,
I'll give it a go when I get back into the office.
I'll juts have to find a way to trigger it via code.
If not I'll use the 'union' function and insert dummy variables.

"Jim Cone" wrote in message
...
David,
If Tools | Options | Chart - "Not plotted ( leave gaps)" is checked then
1. you can change your data for the non-plotted periods to zero or
2. switch to an alternate data source that has zeros for the non-plotted
periods.
Regards,
Jim Cone
San Francisco, CA

David -

Each series in a line chart uses the same categories. So the first
series uses (e.g.) A2:A101 for X and B2:B101 for Y. If you want to use
C22:C41 for the second series' Y values, and have it plot at the same
categories as B22:B41, you need to offset the first data point with
twenty blank cells. This might mean doing some reconstruction in the
worksheet, if you are trying to avoid plotting any data that might be
present in C2:C21.

The alternative is to make an XY Scatter chart. Now your second series
can use A22:A41 and C22:C41 for its X and Y values. If the categories in
A are actual year values, you can even keep series 1 as a line series,
but add series 2 as an XY Scatter series.

People get tangled up in Excel's use of Line and Scatter terminology.
Many of the problems users have with Line charts and their X axes can be
answered with the suggestion to use XY Scatter charts instead. Either
type of series can be formatted the same, in terms of markers, lines,
etc. The difference is in how the X data is treated: a Scatter chart
treats these as actual numerical values, while a Line chart treats them
as non-numerical categories (or as dates in the mislabeled time-scale
category axis option).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

David Adamson wrote:

I currently have a chart series that ranges from 0 to 100 (thanks to Jon
Peltier) to reflect years. I can now add another series of data on top of
that. But what I was wondering is how can I change my series so that I can
show only the data for say year 20 to 40.



I have tried changing 'a' to 20 and 'c' to 40 and it retrieves the correct
data but puts the data over year 0 to 19.



Can someone please tell me what I am missing?



Cheers



David

---------------------------

Charts.Add

ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

ActiveChart.Location Whe=xlLocationAsObject, Name:="Spread Data"

With ActiveChart

.HasTitle = False

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False



End With



With ActiveChart.Axes(xlCategory)

.MinimumScale = 0

.MaximumScale = 100

.MajorUnit = 10

End With



With ActiveChart.Axes(xlValue)

.MinimumScale = 0

End With

'add new data then delete legend

a = 1

r = 6

With Worksheets("Spread Data")

Set Title_Names1 = .Cells(r, 1)

End With



With ActiveChart

.SeriesCollection.NewSeries

.SeriesCollection(2).Name = Title_Names1

End With



'loop through data and make it update

For c = 1 To 100

With Worksheets("Spread Data")

Set Area_rng1 = Range(.Cells(r, 1 + a), .Cells(r, 2 + c))

End With



With ActiveChart

.SeriesCollection(2).values = Area_rng1

End With



Next c

Jon,

Thanks for the tips. I'll have a good play with it and see what I can do



"Jon Peltier" wrote in message
...
David -

Each series in a line chart uses the same categories. So the first
series uses (e.g.) A2:A101 for X and B2:B101 for Y. If you want to use
C22:C41 for the second series' Y values, and have it plot at the same
categories as B22:B41, you need to offset the first data point with
twenty blank cells. This might mean doing some reconstruction in the
worksheet, if you are trying to avoid plotting any data that might be
present in C2:C21.

The alternative is to make an XY Scatter chart. Now your second series
can use A22:A41 and C22:C41 for its X and Y values. If the categories in
A are actual year values, you can even keep series 1 as a line series,
but add series 2 as an XY Scatter series.

People get tangled up in Excel's use of Line and Scatter terminology.
Many of the problems users have with Line charts and their X axes can be
answered with the suggestion to use XY Scatter charts instead. Either
type of series can be formatted the same, in terms of markers, lines,
etc. The difference is in how the X data is treated: a Scatter chart
treats these as actual numerical values, while a Line chart treats them
as non-numerical categories (or as dates in the mislabeled time-scale
category axis option).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______