Creating a dynamic chart

Hi All, I'm a VBA Newbie. The code below works fine to create an embedded chart if the number of rows in the spreadsheet that go into a series collection does not change.
However, what I'm attempting to do is write code that will read the number of rows as the spreadsheet changes, adjust the series collection and generate the same chart type, but with the correct number of series to match the number of rows.
For example, if the number of rows is the source data is 5 can we make the series collection use all 5 lines? If the number of rows changes to 3 can we use the same macro to use the 3 rows of data in the series collection?
Is it possible to do this? Is anyone familiar with this type of situation?
Please let me know. Thanx.




Sub CreatingChart()
Dim TB As Worksheet
Dim j%, lR%
Dim Rng$
Dim S As SeriesCollection
Set TB = ActiveSheet
lR = Cells(65536, 1).End(xlUp).Row
For j = 4 To lR
Rng = TB.Range(TB.Cells(j, 1), TB.Cells(j, 21)).Address
Next j
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(TB.Name).Range(Rng), PlotBy _
:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (1)
ActiveChart.SeriesCollection(1).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(1).Values = TB.Range(TB.Cells(4, 7), TB.Cells(4, 21))
ActiveChart.SeriesCollection(1).Name = TB.Range("B4")
ActiveChart.SeriesCollection (2)
ActiveChart.SeriesCollection(2).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(2).Values = TB.Range(TB.Cells(5, 7), TB.Cells(5, 21))
ActiveChart.SeriesCollection(2).Name = TB.Range("B5")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (3)
ActiveChart.SeriesCollection(3).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(3).Values = TB.Range(TB.Cells(6, 7), TB.Cells(6, 21))
ActiveChart.SeriesCollection(3).Name = TB.Range("B6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (4)
ActiveChart.SeriesCollection(4).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(4).Values = TB.Range(TB.Cells(7, 7), TB.Cells(7, 21))
ActiveChart.SeriesCollection(4).Name = TB.Range("B7")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (5)
ActiveChart.SeriesCollection(5).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(5).Values = TB.Range(TB.Cells(8, 7), TB.Cells(8, 21))
ActiveChart.SeriesCollection(5).Name = TB.Range("B8")
ActiveChart.Location Where:=xlLocationAsObject, Name:=TB.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Text = TB.Range("A1")
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlValue, xlPrimary).HasTitle = False
End With

End Sub

To set up your Series formula so that you can forget the maximum row value and the number of rows you will use over time you can use a dynamic range with a defined name. For example:
[vba]=SERIES(,'WorkbookName.xls'!MyColAData,'WorkbookName.xls'!MyColGData,)[/vba] You then define a range name like "MyColAData" with the formula:
=OFFSET('Sheet 1'!$A$2035,0,0,COUNTA('Sheet 1'!$A:$A))
where:
"Sheet 1" is where the column A data is located, and
"2035" is the first row in the column that you want to plot. The "$A:$A" takes care of the rest of the rows in the column. You can add or remove data rows at will without altering the Series formula.
You also have to define a second range name for the column G data in the same way.

Try it ... you'll love it!

P.S. You can also use a defined list to do the same thing, as I just discovered, but you have to be using Excel 2003.

P.P.S. WELCOME to the wonderful world of VBAX! Glad to have you join us!

Thanks you very much Cyberdude for the warm reception and your quick response!
I will try your suggestion ASAP and let you know the results.
Thanks again