Hi Stepahne,

Got IT....

THANKS MAN

On Jul 23, 8:43 am, anshu wrote:
Hi Stephane,

I have counted the number of rows and put the value in E1000 and the
number of column in AK1.

Then I tried inserting the following code:

sRangeToDraw = "E1:E" & Range("Analysis!E1000") & ",Y1:" &
Range("Analysis!AK1") & Range("Analysis!E1000")
Range ("Analysis!E1000")
ActiveChart.SetSourceData
Source:=Sheets("Analysis").Range(sRangeToDraw) _
, PlotBy:=xlRows

It says compiling Error. Can you please tell me what's wrong here?

thanks,
Anshuman

On Jul 23, 2:16 am, Stephane Quenson

wrote:
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.