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.