Changing of range (Address) to (Cell)

[Kaiyang]

Hi people

I have encounter a problem with the use of range

From a recorded macro, it's listed this way
ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2,A24:M28"), PlotBy:=xlRow

And I edit it to this way
ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2," & Cells(StartX, StartY), Cells(LastX, LastY)), PlotBy:=xlRow

And obviously VBA compiler won't let me go this easily, it happen to give an "evil-comment" on my source range, May i know how can i solve this

Thank You

 

[Andy Pope]

Hi Kaiyang,

Try this, assuming the values in your variables start & last are valid.

With Sheets("Trend")
ActiveChart.SetSourceData Source:=.Range("A1:M2," & _
(.Cells(StartX, StartY).Address & ":" & _
.Cells(LastX, LastY).Address)), PlotBy:=xlRows
End With

Cheers
Andy