Need help resizing embedded chart with VBA

[Joel Mills]

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

 

[Greg Koppel]

You will want to use the following

LastCol = Range("A25").End(xlRight).Column
Set RngToCover = ActiveSheet.Range(Cells(1, 2), Cells( LastCol, 24))

HTH, Greg

 

[Dave Peterson]

I think Greg had a couple of typos:

LastCol = Range("A25").End(xlToRight).Column
Set rngtocover = ActiveSheet.Range(Cells(1, 2), Cells(24, LastCol))

 

[Joel Mills]

Dave,

Thanks for the reply. I'm not sure how to edit the original code to replace
it with the varible. Also will I have to make the chart active by selecting
it before running the macro? I would prefer to list the chart by name and
have the macro resize it based on the changing data.

 

[Dave Peterson]

If you know the name of the chart, you can specify that in the code (or if it's
the only chart on the worksheet, you could use that).

Option Explicit
Sub CoverRangeWithAChart()
Dim LastCol As Long
Dim RngToCover As Range
Dim ChtOb As ChartObject

With ActiveSheet
LastCol = .Range("A25").End(xlToRight).Column
Set RngToCover = .Range("B1", .Cells(24, LastCol))
Set ChtOb = .ChartObjects("chart 1")
'or
'Set ChtOb = .ChartObjects(1)
With ChtOb
.Height = RngToCover.Height ' resize
.Width = RngToCover.Width ' resize
.Top = RngToCover.Top ' reposition
.Left = RngToCover.Left ' reposition
End With
End With

End Sub

If you ctrl-click on your chart, you can see the name in the namebox -- just to
the left of the formula bar.

 

[Joel Mills]

Dave,

Thanks for the help. This worked great.

If you know the name of the chart, you can specify that in the code (or if it's
the only chart on the worksheet, you could use that).

Option Explicit
Sub CoverRangeWithAChart()
Dim LastCol As Long
Dim RngToCover As Range
Dim ChtOb As ChartObject

With ActiveSheet
LastCol = .Range("A25").End(xlToRight).Column
Set RngToCover = .Range("B1", .Cells(24, LastCol))
Set ChtOb = .ChartObjects("chart 1")
'or
'Set ChtOb = .ChartObjects(1)
With ChtOb
.Height = RngToCover.Height ' resize
.Width = RngToCover.Width ' resize
.Top = RngToCover.Top ' reposition
.Left = RngToCover.Left ' reposition
End With
End With

End Sub

If you ctrl-click on your chart, you can see the name in the namebox -- just to
the left of the formula bar.