Charting with VBA - wanting to use .End(xlDown) but getting method failure

[markdarby]

Hi All,
I've read more web posts and tried more things that I can shake a stick at... none seem to work. I've hard coded the name of the data sheet that I'm using for now... but will revert to using the parameter that I pass to the subroutine later.

Sub CreateGraphs(ProjectToGraph As String)

Dim MyChart As Chart
Dim DataRange As Range


ProjectToGraph = "ARLG"

Worksheets("ARLG").Activate

With Worksheets("ARLG").Shapes.AddChart2(227, xlLineStacked, 30, 30, 600, 400).Chart

'===================================================================================================

' This line works without any issue. Since I'll never know how many rows are in the data field, I would have to edit the range every time I created the chart.
'.SetSourceData Range("ARLG_Data!$A$1:$C$35")

' Wanting to dynamically set the last row.... but this fails every time with an error: "Method 'SetSourceData' of object'_Chart' failed"
.SetSourceData Range("ARLG_Data!$A$1:$C$1"), Range("ARLG_Data!$A$1:$C$1").End(xlDown)




'=================================================================================================== .HasTitle = True
.ChartTitle.Text = ProjectToGraph
.SetElement (msoElementLegendBottom)
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Labor Hours"
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd mmm"
End With

' Need to add a vertical line for the date....

End Sub


I'd be grateful for help that anyone can provide.

Mark

 

[JLGWhiz]

.SetSourceData sheets("ARLG_Data").Range("$A$1", Sheets("ARLG_Data").Cells(Rows.Count, 3).End(xlUp))

Or if you have other data below the range you want to use, then

.SetSourceData sheets("ARLG_Data").Range("$A$1", Sheets("ARLG_Data").Cells(1, 3).End(xlDown))