Dynamic chart range VBA.

Resolved · Medium Priority · Version 2016

Wed 29 Apr 2020, 16:21 Delegate Callum said...

Callum has attended:
Excel Advanced course

Dynamic chart range VBA.

Hello, I have a question about VBA.

I require creating a chart using a particular variable for cities across the UK.

I have set up a macro that generates this chart, however sometimes I am using more cities in one case than another time. When I use the macro, it only creates a table for the number of cities I used to create the initial macro.

What VBA do I need to allow the range to be dynamic, so when I have more rows of data, the chart source range in the macro automatically adjusts to this?

Thank you.

Thu 30 Apr 2020, 11:48 Trainer Jens said...

RE: Dynamic chart range VBA.

Hi Callum,

Thank you for the forum question.

Recorded macros cannot handle changes. They can only do exactly what you did, when you recorded it.

Chart VBA is very complicated and depends on a lot of variables (names, ranges, formats, and much more). We have Chart VBA on our Excel VBA Advanced course and you will need the VBA knowledge on advanced level to be able to understand and write the code.

You can reference the whole range by using CurrentRegion and then the chart automatically will update if you have more cities.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector