limiting source ranges when building charts in vba

[Guest]

Hi.
I'm making quite a lot of use of this chat, so for all the answers so far, a
big thank you!
Now. I am builduing charts in vba with 3 different source ranges.
First question: is it possible to name the series after the ranges rather
than through their number?
Second, the soure series are named ranges that are not always totally filled
with data. How do I specify that the boundary of the range to take is the
last cell that contains data?
Thanks a lot

 

[cholley]

Try naming your data range using the offset function; e.g., when you create
the name for your first series, the reference will be something like "=offset
($g$3,0,0,$g$1,1)". In this example, cell "g1" will be a formula that
determines how long the series is; one way to do this is to use a match
function (e.g., "=match("",g3:g30,0)-1"). Next, in your chart, use the
dynamic name you created to define your series. You'll need to make sure
that your X-axis series is also defined in the same way.

Good Luck.

 

[Guest]

Thanks. But I'm not sure I understand quite how to work it.
Take the following as an example.
My first series is defined like this:
Worksheets("DataDownload").Range("ag4:ag2000").Name = "LongCumReturn"
In the chart creation sub, I have sources defined like this:
..SetSourceData Source:=Sheets("DataDownload").Range("LongCumReturn,
ShortCumReturn, LSRatio"), PlotBy:=xlColumns
I could count the number of filled cells in the first series using the
COUNTA function and store the data on a sheet. The value would then be
defined as this:
x = Worksheets("Background").Cells("Datanum").Value
How do you suggest I do this? I checked the offset function in the Help
menu, but I see Offset(rowOffset, ColumnOffset) whereas you seem to indicate
4 arguments.
Thanks!

Benoit