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.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"George Rickerson" <George wrote in
message ...
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.

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"George Rickerson" <George wrote in
message ...
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.

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/...umnChart1.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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"George Rickerson" wrote in
message ...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"George Rickerson" <George wrote in
message ...
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.