Source Data for the Chart

[anshu]

Hi All,

One Small Issue:

I am drawing a chart and this is the source data for that.

ActiveChart.SetSourceData
Source:=Sheets("Analysis").Range("E1:E11,Y1:AJ11") _
, PlotBy:=xlRows

Is there any way I can put a variable in place of 11 in the range.
This variable will count the data in column E.

Also, Column AJ is the extreme, I may not go upto that. Is there
anyway I can make that as a variable also. This variable counts the
data in Y1:AJ1 and goes only that many columns to the right of Y1

Let me know if you people can help with this

Thanks,
Anshuman

 

[Stephane Quenson]

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.

 

[anshu]

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

 

[anshu]

Hi Stepahne,

Got IT....

THANKS MAN

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

 

[Stephane Quenson]

CORRECTED -- One line containing Range("Analysis!B6") was mistakenly inserted
in the code. Correct code reposted below

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")
ActiveChart.SetSourceData
Source:=Sheets("Analysis").Range(sRangeToDraw) _
, PlotBy:=xlRows