Search code examples
excelvbaexcel-charts

Excel VBA - resize ChartArea to fit range

I'm trying to resize a chart to fit over a specific range of cells. The issue is that it isn't setting the chart size exactly to the range size. Below is a random chart demonstrating the issue. I've highlighted the range for easier understanding: Yellow - to fit ChartArea, Blue - to fit PlotArea. As can be seen, the ChartArea extends beyond the range, both on the right and at the bottom, this subsequently causes the PlotArea to shift out of place (size of the PlotArea seems fine, just position is incorrect)

enter image description here

Below is the code I've tried:

Sub xyz()

Dim chrt As Chart
Dim rngCA As Range
Dim rngPA As Range

Set chrt = ChartObjects("Chart 1").Chart
Set rngCA = Range("C5:I16")
Set rngPA = Range("E7:H14")

    With chrt.ChartArea
        .Height = rngCA.Height
        .Width = rngCA.Width
        .Top = rngCA.Top
        .Left = rngCA.Left
    End With

    With chrt.PlotArea
        .InsideHeight = rngPA.Height
        .InsideWidth = rngPA.Width
        .InsideTop = rngPA.Top - rngCA.Top
        .InsideLeft = rngPA.Left - rngCA.Left
    End With

End Sub

Any assistance will be appreciated

Solution

  • Try setting the properties for the ChartObject instead of the ChartArea...

    With chrt.Parent 'refers to chartobject
    .Height = rngCA.Height
    .Width = rngCA.Width
    .Top = rngCA.Top
    .Left = rngCA.Left
End With

    or

    With ChartObjects("Chart 1")
    .Height = rngCA.Height
    .Width = rngCA.Width
    .Top = rngCA.Top
    .Left = rngCA.Left
End With