How to Resize the Chart Plot Area Using VBA in Excel – 3 Steps

Download Practice Workbook

Download the practice workbook.


Step 1 – Populate the Dataset

  • The sample dataset showcases Month-wise Sales in 2 years.

Populate Dataset with Necessary Components to resize chart plot area using VBA in Excel

Read More: How to Resize Chart Area Without Resizing Plot Area in Excel


Step  2 – Insert a Chart

  • Select the entire dataset.
  • Go to the Insert tab >> choose Insert Column or Bar Chart >> Clustered Column.

Insert a Chart to resize chart plot area using vba in Excel

A chart will be displayed.

Read More: How to Create an Area Chart in Excel (6 Suitable Examples)


Similar Readings


Step 3 – Use a VBA Code to Resize the Chart Plot Area

  • Select the chart area and go to the Developer tab >> select Visual Basic.

Apply VBA Code to Resize Chart Plot Area in Excel

Note: you can also open the VBA Editor by pressing ALT + F11.

  • Select Insert >> Module>> Module1.

  • Enter the following code in Module 1.
Sub Resize_Chart_Plot_Area()
  Dim ch1 As Chart, plot_height As Double, plot_width As Double
  Set ch1 = ActiveChart
  plot_height = ch1.PlotArea.Height: plot_width = ch1.PlotArea.Width
  ch1.Parent.Height = 225: ch1.Parent.Width = 550
  ch1.PlotArea.Height = 150: ch1.PlotArea.Width = 500
End Sub

Code Breakdown:

  • The Resize_Chart_Plot_Area  Sub Procedure is created.
  • ch1 is declared as Chart and plot_height and plot_width as Double.
  • ch1 is set as ActiveChart.
  • plot_height is set to ch1.PlotArea.Height and plot_width to ch1.PlotArea.Width. The variable value is replaced with 150 and 500 for Plot Area height and width.
  • The chart height and width is changed to 225 and 550 using the Parent.Height and Parent.Width commands.
  • The End Sub stops the code.

VBA code to resize chart plot area in Excel

  • Run the code by pressing F5.

The chart will automatically be resized:

Read More: Excel Area Chart Data Label & Position


How to Move a Chart to a Specific Cell with VBA in Excel

  • Open a new module and enter the following code:
Sub Relocate_Chart()
Dim ch1 As Chart
On Error Resume Next
    Set ch1 = ActiveChart
On Error GoTo 0
If ch1 Is Nothing Then
    MsgBox "Select the Chart First"
    Exit Sub
End If
Dim r As Range
Set r = Application.InputBox("Select the Range", "Range Selection", Type:=8)
ch1.Parent.Top = r.Top
ch1.Parent.Left = r.Left
ch1.Parent.Height = r.Height
ch1.Parent.Width = r.Width
ch1.Location xlLocationAsObject, r.Parent.Name
End Sub

Code Breakdown:

  • The Relocate_Chart Sub Procedure is created.
  • A MsgBox is set to be displayed when the chart is not selected. It will display: “Select the Chart First”.
  • r is set as Range and an InputBox command is inserted to select the range.
  • The chart is set to the selected range box.

How to Move Chart to Specific Cell with VBA in Excel

Assign macro to resize chart plot area using vba in excel

  • Select the Macro Relocate_Chart.
  • Click OK.

  • Select the range to place the chart.

See the output in the GIF below.


Practice Section

Practice here.

Practice Section

 


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

2 Comments
  1. Hi Sir,
    Have a good day.
    If i want to resize the plot area but my chart area need to be fixed. Can it be done? If yws, then what modification should be needed?

    • Reply Avatar photo
      Fahim Shahriyar Dipto Jan 11, 2023 at 2:00 PM

      Hello Terry,
      It’s really nice to hear from you. In your query, you wanted to know about changing the plot area where the chart area will be the same. The VBA code needs to be modified a bit. I have attached the code below.
      Sub Resize_Chart_Plot_Area()
      Dim ch1 As Chart, plot_height As Double, plot_width As Double
      Set ch1 = ActiveChart
      chart_height = ch1.PlotArea.Height: chart_width = ch1.PlotArea.Width
      chart_height = ch1.PlotArea.Height: chart_width = ch1.PlotArea.Width
      ch1.PlotArea.Height = 150: ch1.PlotArea.Width = 400
      End Sub
      Run the code with the F5 key and it will change the plot area without changing the plot area.
      Have a great day.
      Regards,
      Fahim Shahriyar Dipto
      Excel & VBA Content Developer.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo