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

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

"Joel Mills" wrote in message
...
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

I think Greg had a couple of typos:

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



Greg Koppel wrote:

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

"Joel Mills" wrote in message
...
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



--

Dave Peterson

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" wrote in message
...
I think Greg had a couple of typos:

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



Greg Koppel wrote:

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

"Joel Mills" wrote in message
...
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



--

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

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" wrote in message
...
I think Greg had a couple of typos:

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



Greg Koppel wrote:

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

"Joel Mills" wrote in message
...
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



--

Dave Peterson


--

Dave Peterson

Dave,

Thanks for the help. This worked great.


"Dave Peterson" wrote in message
...
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 wrote:

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" wrote in message
...
I think Greg had a couple of typos:

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



Greg Koppel wrote:

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

"Joel Mills" wrote in message
...
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



--

Dave Peterson


--

Dave Peterson