Automatically adjusting chart height VBA

[Lewzerrrr]

Hey,

I created a 2d column graph that’s dynamic based up on a drop down list. My only problem with it is each individual graph looks completely different as the height I believe is fixed. What I need is for it to be dynamic so it doesn’t try to scale to said height adjustments. Having a fixed width is perfectly fine..

For example, graph 1 may have only 2 entries on the vertical axis so this looks huge if it’s fixed to B2:AA50.. then graph 2 may have 20 entries on the vertical axis so it will look small and unreadable as it fits to B2:AA50. What I’m thinking of is for each entry it has a fixed height and then for every entry it cumulates this but I’m unsure how to execute this as never worked with charts before.

Any help is appreciated

 

[Worf]

Hey

The code below will adjust chart height and width based on its data:

Sub Height_Width()
Dim cho As ChartObject, r As Range


For Each cho In ActiveSheet.ChartObjects
    Select Case cho.Chart.ChartType
        
        Case 51     ' vertical columns
            Set r = [a1].Resize(, cho.Chart.SeriesCollection(1).Points.Count)
            cho.Width = r.Width
            Set r = [a1].Resize(cho.Chart.Axes(xlValue).MaximumScale / 5)
            cho.Height = r.Height
        
        Case 57     ' horizontal bars
            Set r = [a1].Resize(cho.Chart.SeriesCollection(1).Points.Count * 5)
            cho.Height = r.Height
            Set r = [a1].Resize(, cho.Chart.Axes(xlValue).MaximumScale / 5)
            cho.Width = r.Width
    End Select
Next
End Sub