Resize and Relocate Chart
Charts and Visualization VBA

Resize and Relocate the Chart using Excel Range

In this article you will learn a trick to resize and relocate a chart using VBA. We have created a shortcut key Shift+Ctrl+R to resize and relocate active chart. You just need to select the range and your selected chart will be relocated and resized according to selected range. You can move the chart to another worksheet also.

Below is the VBA Code-

Sub Resize_Chart()

Dim cht As Chart

On Error Resume Next

    Set cht = ActiveChart

On Error GoTo 0

If cht Is Nothing Then

    MsgBox "Please select the chart"

    Exit Sub

End If

Dim rng As Range

Set rng = Application.InputBox("Please select the Range", "Range Selection", Type:=8)

'Chart Object is the parent of Chart

cht.Parent.Top = rng.Top

cht.Parent.Left = rng.Left

cht.Parent.Height = rng.Height

cht.Parent.Width = rng.Width

cht.Location xlLocationAsObject, rng.Parent.Name   'Worksheet name is the parent of range

End Sub

 

Just copy this code and paste in VBA Module. You add the shortcut key to run this macro. To add a shortcut key

  • Go to View Tab >>Macros>>View Macros>>
  • Select Resize Chart Macro and Click on Options button
  • Put capital “R” in shortcut key box.

Click here to download this Excel workbook.

 

Watch the step by step video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com