Chart to resize plot area based on horizontal axis data

Not doing so good with this one. On this chart, in A1 I select either A or B and you can see how it pulls from the table in A15:B21.

On the before mock-up, you select B from A1 and the horizontal axis is changed to the new data which is significantly shorter than the A category.

I used the macro recorder to record me changing the plot area size, but it actually changes the Chart Area not the plot area.

I've searched for a few macros and have found some to increase the size, but not one which will increase to the maximum available size or then shrink back to the size of category A.

Any thoughts?

try this event code on the Before worksheet

Please Login or Register  to view this content.

Thank you Andy. This works great.