Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
limiting source ranges when building charts in vba
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
limiting source ranges when building charts in vba
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. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
limiting source ranges when building charts in vba
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").Nam e = "LongCumReturn" In the chart creation sub, I have sources defined like this: ..SetSourceData Source:=Sheets("DataDownload").Range("LongCumRetur n, 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 "cholley" wrote: 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
limiting chart source data with criteria? | Charts and Charting in Excel | |||
Limiting Sort Ranges | Excel Discussion (Misc queries) | |||
Limiting ranges when building charts in vba | Excel Discussion (Misc queries) | |||
dynamically building references to named ranges | Excel Discussion (Misc queries) | |||
tutorial for building charts? | Excel Discussion (Misc queries) |