Match Graph Width to Inputed Data

jjlafond

Board Regular
Joined
Jul 17, 2014
Messages
56
Hello,

I am graphing (Line graph)a data series, with data going across the page in a single row.

I slowly add data to the end of this series, and an existing macro automatically expands the range of the graph to include the new data.

I would like the size of the graph to also expand automatically (VBA), and (roughly) line up with the data above it, so that the visual graph is directly beneath the data numbers.

Any ideas on how to do this?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You'll need to experiment a little due to where you have your series' placed but this will get you going:

Code:
Sub ChartWidth()


Dim gWidth As Long
Dim mChart As Chart


gWidth = Range("G2:N2").Width


Set mChart = ActiveChart


mChart.ChartArea.Width = gWidth


End Sub
 
Upvote 0
Say your data spans columns A:E, and you have just updated it to be this way.

You can say:

Code:
Sub ResizeChart()
    n = Cells(1, Columns.Count).End(xlToLeft).Column
    ActiveSheet.Shapes("Chart 1").Width = Range(Cells(1, 1), Cells(1, n)).Width
End Sub
Hope this helps,

Chris.

P.S. What I'd really like to be able to do with this is to be able to use some kind of "ActiveShape" command. If anyone knows of a way of doing this I'd love to hear it.
 
Last edited:
Upvote 0
Thanks for the responses! I took concepts from both to make it more efficient and user-friendly in my code. Its all working perfectly!

Code:
Width = Range(Range("C18"), Range("C18").End(xlToRight)).Width
ActiveSheet.Shapes("Chart1").Width = Width
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top