VBA dynamic charts

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.

dflak

They are working as you rightly mentioned. But when you delete a country, the legend sign (the pink line) on the chart still remains, which looks untidy. I can find a solution to this. Any Ideas?

Thank you dflak!!