The string used for the Range function does not need to be a fixed string. So
you could have two cells in the sheet where the chart is, let's say B6 and B7
that contains the last row and last column to consider in the chart. Note
that these cells can contain a formula as well, like =COUNT(E1:E1000) for
example.

Then you could put the following lines in your code:
sRangeToDraw = "E1:E" & Range("Analysis!B6") & ",Y1:" & Range("Analysis!B7")
& Range("Analysis!B6")
Range("Analysis!B6")
ActiveChart.SetSourceData
Source:=Sheets("Analysis").Range(sRangeToDraw) _
, PlotBy:=xlRows


Stephane.