How to align the size of Plot Areas|VBA & Macros|Excel Forum|My Online Training Hub

Hello,

"Small Multiples are a great way to visualise a lot of related data in a small space and still make comparisons.
The key is that they all must share the same axis scale, so they can be compared equally.
Because they all share the same axis, we can use one axis for each row and then remove them from the other charts to create a less cluttered look.
Likewise for the horizontal axis."

Problem: When you remove an axis (x or y) the size of the plot area changes. You can align the plot area manually by placing the chart without axis on top of the chart with (set the fill of the chart without axis to No Fill) and then fidle around. There a maximum of 3 charts to be ajusted: w/o x axis; w/o y axis; w/o y and x axis. I hope it gets clear when you look at the atached file.

So tried to simply print out the original ? ActiveChart.PlotArea.width and ? ActiveChart.PlotArea.width and then copy it to the duplicated chart(s): 

Sub PlotAreaSize()
 ActiveChart.PlotArea.Width = xy
 ActiveChart.PlotArea.Height = yz
End Sub

But it did not render the intended equal size. I also tried reapplying the original size after deleting the axis with the same luck. You can see 4 different sizes as result in the attached file (small and big distortions) and also the position of the remaining axis seems to be shifted (inside / up).

Sub ReapplyPlotAreaSize()

Dim w As Variant
Dim h As Variant

w = ActiveChart.PlotArea.Width
h = ActiveChart.PlotArea.Height

 ActiveChart.Axes(xlValue).Delete                                 'comment out if not needed
 ActiveChart.Axes(xlCategory).Delete                            'comment out if not needed
 ActiveChart.ChartArea.Format.Fill.Visible = msoFalse     'to compare the results against original chart (place on top)

ActiveChart.PlotArea.Width = w
ActiveChart.PlotArea.Height = h

End Sub

What is the problem and what would be the correct way to align the Plot Area size? Any way to fix the Plot area position?

Thanks,
Matthias