Source Data for the Chart

  • Thread starter Thread starter anshu
  • Start date Start date
A

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
 
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.
 
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
 
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
Click to expand...
 
CORRECTED -- One line containing Range("Analysis!B6") was mistakenly inserted
in the code. Correct code reposted below

Stephane Quenson said:
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:
Click to expand...

sRangeToDraw = "E1:E" & Range("Analysis!B6") & ",Y1:" & _
Range("Analysis!B7") & Range("Analysis!B6")
ActiveChart.SetSourceData
Source:=Sheets("Analysis").Range(sRangeToDraw) _
, PlotBy:=xlRows
 
Back
Top