Thread: Variable series length/range

Thanks for the help with adding new series with a macro. The procedure from
the Quick Chart VBA site worked great.

Is there some other way to specify the range of cells used in a series? As
I add rows of data, I would like to group some series together (based on date
data was taken, etc). Below is what I'm currently trying. I wanted to use a
variable for the number of rows selected for the data series. Substituting
Cells( , ) for Range(" ") has worked for me in the past for general things
like Copy and Paste, but it produces an error when I use it after ActiveSheet.

Sheets("Summary").Select
same_cal = 1
10
If Range("B5").Value = Cells(5 + same_cal, 2).Value Then
Cells(5 + same_cal, 2).Select
Selection.ClearContents
same_cal = same_cal + 1
GoTo 10
End If

If same_cal > 1 Then
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Cells(5, 2) -- vs. Range("B5")
.Values = ActiveSheet.Range(Cells(5, 5), Cells(5 + same_cal, 5))
--vs. Range("E5:??")
.XValues = ActiveSheet.Range(Cells(5, 1), Cells(5 + same_cal,
1)) --vs. Range("A5:??")
End With
ElseIf same_cal = 1 Then
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("B5")
.Values = ActiveSheet.Range("E5")
.XValues = ActiveSheet.Range("A5")
End With
End If

1. You could make non-VBA dynamic charts:

http://peltiertech.com/Excel/Charts/Dynamics.html

2. You could replace Range("B5") with Range("B5:B10"). If you don't know the
last row yet, write code that figures it out, and use Range("B5:B" &
CStr(iLastRow)). Or use Range("B5").Resize(10) or Cells(5,
2).Resize(iNumberOfRows) or any combination of these.

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

"JessK" <JessK@discussions.microsoft.com> wrote in message
news:0282DE5F-C7BD-41A6-85E6-787F341D7254@microsoft.com...
> Thanks for the help with adding new series with a macro. The procedure
> from
> the Quick Chart VBA site worked great.
>
> Is there some other way to specify the range of cells used in a series?
> As
> I add rows of data, I would like to group some series together (based on
> date
> data was taken, etc). Below is what I'm currently trying. I wanted to
> use a
> variable for the number of rows selected for the data series.
> Substituting
> Cells( , ) for Range(" ") has worked for me in the past for general things
> like Copy and Paste, but it produces an error when I use it after
> ActiveSheet.
>
> Sheets("Summary").Select
> same_cal = 1
> 10
> If Range("B5").Value = Cells(5 + same_cal, 2).Value Then
> Cells(5 + same_cal, 2).Select
> Selection.ClearContents
> same_cal = same_cal + 1
> GoTo 10
> End If
>
> If same_cal > 1 Then
> ActiveSheet.ChartObjects("Chart 1").Activate
> With ActiveChart.SeriesCollection.NewSeries
> .Name = ActiveSheet.Cells(5, 2) -- vs. Range("B5")
> .Values = ActiveSheet.Range(Cells(5, 5), Cells(5 + same_cal,
> 5))
> --vs. Range("E5:??")
> .XValues = ActiveSheet.Range(Cells(5, 1), Cells(5 + same_cal,
> 1)) --vs. Range("A5:??")
> End With
> ElseIf same_cal = 1 Then
> ActiveSheet.ChartObjects("Chart 1").Activate
> With ActiveChart.SeriesCollection.NewSeries
> .Name = ActiveSheet.Range("B5")
> .Values = ActiveSheet.Range("E5")
> .XValues = ActiveSheet.Range("A5")
> End With
> End If
>