VBA Chart code question for dynamic data

[squeekee35]

This site moves so fast and there is so much information on it. I can't find what i am looking for in past posts....i really hope some one can help me.

I created a VBA to chart data....a simple one (shown below my question)....

my question is how can i get the VBA to chart variable data...meaning one day i have 25 points on my graph and the next day i have 30 and maybe the day after that i only have 20. I can't seem to figure out how to change the R25 to be a varialbe number. any help would be appreciated
thankyou
angel

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R25C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C2:R25C2"
ActiveChart.SeriesCollection(1).Name = "=""Chart One"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Chart One"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"These are the x values"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"These are the y values"
End With

 

[Bill_Biggs]

Have you tried making it a pivot chart?

 

[Bill_Biggs]

Here. This is the simple way.

Sub UsedRange_Example_Row() 'Untested
Dim A As Integer
Dim LastRow As Long
With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With
A = LastRow
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R" & A & "C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C2:R" & A & "C2"
ActiveChart.SeriesCollection(1).Name = "=""Chart One"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Chart One"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"These are the x values"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"These are the y values"
End With
End Sub

 

[squeekee35]

Thanx---

This worked out well but i had to change...

Lastrow= .rows(.rows.count).row

to

lastrow= activecell.end(xlDown)

thankyou thankyou thankyou

angel