I am using Excel 2000 and found the following code at Jon Peltier's
website. I have an embedded chart named "Curve Chart". I would like for
this Chart (Object) to resize based on a variable range. This sheet contains
more than one chart and I would like for the code to select this chart and
not have it based on an active chart.

Row 25 has data that can span various columns. What I need is a way to
determine the last cell containing data on row 25 and then use that column
and row 24 to determine the range to use. "B1" will always be the starting
point of the range, and Row 24 will always be the ending point of the range,
but the column can change. I am able to get the results I want by selecting
the chart and editing the following code, but I'm sure there must be a way
to make the chart resize depending on the data in row 25.

Any help would be appreciated.


Sub CoverRangeWithAChart()
Dim RngToCover As Range
Dim ChtOb As ChartObject
Set RngToCover = ActiveSheet.Range("B1:AU24")
Set ChtOb = ActiveChart.Parent
ChtOb.Height = RngToCover.Height ' resize
ChtOb.Width = RngToCover.Width ' resize
ChtOb.Top = RngToCover.Top ' reposition
ChtOb.Left = RngToCover.Left ' reposition
End Sub