Setting source data range with Charts

[Guest]

I'm trying to figure out how to define the source data range when the range
is not predetermined, when adding a chart in VBA. I can't just do "A1:B3",
because it will be an unknown number of rows (though always two columns). If
cells worked (as I've tried below), I could just use a variable for the
appropriate row reference, but no go so far. Any ideas? Thanks a lot.

With Charts(1)
.Location where:=xlLocationAsNewSheet
.SetSourceData Source:=Sheets("Sheet1").Range(Cells(1, 1),
Cells(2, 2)), PlotBy:=xlColumns
.ChartType = xlBarClustered
End With

 

[William]

Hi

Sub ChartRange()
Dim r As Range, c As Chart
With Sheets("Sheet1")
Set r = .Range(.Range("A1"), _
..Range("A" & Rows.Count).End(xlUp).Offset(0, 1))
End With
Set c = Charts.Add
c.Location Where:=xlLocationAsNewSheet, Name:="MyChart"
c.SetSourceData Source:=r, PlotBy:=xlColumns
End Sub


--
XL2002
Regards

William

(e-mail address removed)

| I'm trying to figure out how to define the source data range when the
range
| is not predetermined, when adding a chart in VBA. I can't just do
"A1:B3",
| because it will be an unknown number of rows (though always two columns).
If
| cells worked (as I've tried below), I could just use a variable for the
| appropriate row reference, but no go so far. Any ideas? Thanks a lot.
|
| With Charts(1)
| .Location where:=xlLocationAsNewSheet
| .SetSourceData Source:=Sheets("Sheet1").Range(Cells(1, 1),
| Cells(2, 2)), PlotBy:=xlColumns
| .ChartType = xlBarClustered
| End With

 

[Guest]

William: exactly the help I was also looking for. Thanks and Happy New Year
jeff