Variable series length/range

[Guest]

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

 

[Jon Peltier]

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/
_______