VBA dynamic chart

[Williamm]

I have a range consisting of countries on the Y-axis and years on the X-axis (I am tracking the inflation change over time for each country with a line chart). I want to create a dynamic chart for my sample which will update when a new country is added. The data in the range is linked to another source, hence the number of rows can increase or deacrease. I am happy to use VBA. I don't want to turn the country name into a table and make the dynamic chart based on that, because the legend sign on the chart for empty cells don't disappear. When I turn the whole range into a table and build the chart, it does not do the job either, unless I enter new countries and respective data manually. I tried to use OFFSET&COUNTA, but no luck. The latter works only if I add new date (a new year, new column), but not when I add a new country (new row). This is why I think the only way to do is using VBA.


I will appreciate your help.

 

[vletm]

It would be much easier/nicer to continue already opened case
http://forum.chandoo.org/threads/dynamic-charts.32545/
than open same case again.
Or at least You would try to update Your sample file.

 

[Williamm]

Vltem,

Now I am looking for a VBA solution, since the previous solution are not exactly what I want. THis is the reason I opened a new thread.

 

[vletm]

as well previous time
You wrote to 'Ask an Excel Question'
which moved to 'Discuss Data Visualizations and Charting'.
why not to 'VBA Macros'?
... How can I get it disappear. The rest is just fine, thanks.
Okay

 

[Williamm]

will do that

 

[vletm]

You can add & delete rows & columns
Chart will update with both cases.

 

[Williamm]

Vltem,

Thank you for staying on my case.

The problem is, when I delete on of the column, the legend on the chart still remains. (f.e., when I delete Armenia and the respective row, the blue legend line on the right part of the chart remains, which makes the whole chart look kind of untidy)

 

[vletm]

The problem?
No problem even with Armenia!
Before..

and after...

maybe 'challenge' is that You didn't check, or what?

 

[Williamm]

I've checked every time you posted sth. Maybe I did not manage to explain it properly. I will use an INDEX&MATCH formula to link column A (countries) to a source sheet (and vlookup for the inflation numbers). I will use this formula for the first 8 cells (A2:A7), because I have 8 countries in my source sheet. The data on my source sheet updates constantly together with the number of countries. Hence I may have 8, 7, 6.... or 1 county on the "Data" sheet in Column A. Regardless of the number of countries, the formula in the cells A1:A8 will remain, but the cells will be empty. Because of the latter, the legend sign will still be on the chart.

Highligh Armenia and his data and click backspace, don't delete the whole series. Let the cells stay empty.

 

[vletm]

when I delete Armenia and the respective row
and
next You don't delete
...
then I can say just Take Care

 

[Williamm]

I think you know what I mean.

Nevertheless, thank you for your time