I have a horizontal bar chart Chart(549) on a sheet that includes data from a dynamic set of rows.
Let NumRows = UBound(UniqueFoRs, 1)
I wish the chart top Bar of the chart to remain in a fixed and constant position on the sheet.
I wish the Height of Chart(549) to increase/decrease according to NumRows.
The equation to change Chart(549).Height cannot be a simple ratio because that fails to take account of the top section of the Chart. In other words the below algorithm cannot work
ActiveSheet.Shapes("Chart 579").ScaleHeight 1.20
because this will move the position of the first bar in the chart downwards (because the Height is being scaled upwards) - it is the first bar in the chart that must remain in a fixed position.
Are there any other ways to manipulate the height of a chart like this?
I have coded Zoom = 100% during the running of the Macro, so you can count on that occurring.
A picture is attached so you can see the alignment between chart and rows of data – the number of rows will change.