Find range for plotarea

[Guest]

Hi -
I hate to take up people's time with such a simple problem, but I have
looked for the answer everywhere I can think of and have come up dry. So
thanks for your help in advance.

I need to find the data range for a chart. My goal is to change the data
range to include new data I have added to the worksheet. The number of rows
of new data may be different each time I run this macro, and, of course, the
data range for the chart will be different every time I run the macro, since
I will have changed it previously.

I have scoured the plotarea and chart objects as well as the range object
and can't figure it out. I have recorded a macro in which I select the
plotarea and then use right click to get to the dialog box that specifies the
data range, but the VB resulting from this recording doesn't include anything
other than making the chart object active.

thanks
George.

 

[Jon Peltier]

The 'data range' is not exposed in any way to VBA. You could use John
Walkenbach's series formula class module

http://www.j-walk.com/ss/excel/tips/tip83.htm

to determine the range of X values and all the ranges of Y values, and use
Union to determine the combined range. Keep in mind that this data range is
often stated by Excel as being too complex: all you need is one series with
a different number of points, or the series plot order rearranged, or two
different sets of X values used in the chart.

- Jon

 

[Guest]

Hi, Jon -
Thanks for your response. Actually, I think I may have found an indirect
way to find the range, I'd be interested in your assessment of this idea. If
I name the range, then I think I can use the RefersTo property of the Names
object to get the range specification in A1 format (as a string). Then I can
extract the numeric part of the lower right corner of the range, change it to
a number, increment it by the number of rows I have added, change it back to
a string, rebuild the range spec and redefine the range name to refer to the
new range. The part I haven't solved yet is getting the chart then to be
graphing based on the new range.

This is just one little part of a much larger program that is presenting
me with much more challenging issues, so it may be a while before I am able
to test this idea.

thanks,
George.

 

[Jon Peltier]

That's an interesting idea. If you use names, though, you can make it
simpler. If you are not adding or removing series, you can use simple
dynamic names for the X and Y values of each series without requiring any
VBA at all:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
http://peltiertech.com/Excel/Charts/Dynamics.html

If you are changing both the width and the height of the range, define a
name that grows and shrinks in both directions. Then use a Worksheet_Change
event to update the chart, using SetSourceData. Say the name is called
"MyChartData", and it measures the number of rows and columns using
COUNT/COUNTA with OFFSET/INDEX. Then open the code module corresponding to
the worksheet (right click the worksheet tab, then choose View Code). In the
left hand dropdown of the code module, select Worksheet, and in the right
hand dropdown, select Change. Modify the resulting procedure chunk so it
reads:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("MyChartData")) Is Nothing Then
Me.ChartObjects(1).Chart.SetSourceData Source:=Me.Range("MyChartData")
End If
End Sub


- Jon

 

[Guest]

Thanks, Jon -
The method you described I didn't try, but it got me to thinking and
searching around in Excel Help, and I found the stuff on tables. I made the
range for the plotarea a table, and now when I copy a new row of data at the
bottom of the table, the table (and the data range for the chart's plotarea)
is automatically extended. No VBA code necessary.

It took me a month, but I finally have a working macro that gets a row
from a table in an Outlook mailitem, copies it into the Excel spreadsheet,
thereby automatically updating the chart. It would not have taken so long,
but I didn't really understand how to work with Selection objects in Outlook,
especially when a table is involved.

thanks for your help.