Charts: Get source data range in VBA?

[Clif McIrvin]

Excel 2003

I'm wanting to "resize" the data area of a chart using a macro.

I have added new data below existing data, and want to extend the source
data area to include the newly added data in the chart.

I have found the SetSourceData method but I don't know how to identify
the current data area. Is this possible?

Situation: I have inherited a set of worksheets (of similiar structure)
which contain historical data and are updated once a month. The existing
procedure is to add the new data at the bottom of the existing data,
then select the chart and stretch the source data outline to include the
new data.

I am writing a macro to automate this copy and paste process; but
stalled out when I discovered that I don't know how to obtain the
current data area using VBA.

(Not sure what additional information might be helpful ... so am posting
as written.)

 

[Clif McIrvin]

I had a "well, duh!" moment.

The macro recorder had given me:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData _
Source:=Sheets("5000").Range("B1:C246"), _
PlotBy:=xlColumns

Since all the charts *begin* at the same place, I realized I can
hard-code that into my macro.

I ended up with:

With ActiveSheet
.ChartObjects(chartName).Activate
ActiveChart.SetSourceData _
Source:=.Range(.Cells(1, 2), _
.Cells(newCells.End(xlDown).Row, 3)), _
PlotBy:=xlColumns
End With

which is doing just what I wanted.
(newCells is a range object pointing to the newly added cells below the
previous data.)