Create XY Scatter plot Bubble

I have a sample dataset with 3 series on Y axis and year range on X axis. I have plotted XY Scatter plot manually, however if Y axis data range is more than 5 data series (e.g. 10, 15) it become very combursom to plot a chart.following are the steps to create chart

1. Insert blank row against each column
2. Enter data series for each column (for this example 3)
3. Insert Scatter Bubble plot based on the new dataset

I have enclosed a small dataset for reference. I would appreciate any help for creating VBA based Macro

Originally Posted by MrShorty

I'm not sure I understand. What do you want the VBA to do that is not already accomplished by the existing chart? Without VBA, here's how I would change what you have:

1) column G =IF(ISBLANK($A5),NA(),A5)
2) copy that across and adjust references/value if false argument to return the correct value to each column. For example column H becomes =IF(ISBLANK($A5),NA(),3)
3) copy G5:M5 and paste down to the bottom row you will ever use.
4) Adjust chart source area to include all of the rows you will ever use.

The use of NA() causes the chart to ignore those rows in the source data. As rows are added to the input data, they should automatically show up in the output and chart data, and the chart should update to reflect the added data. No need for VBA.

Dear MrShorty,

Thank you for the quick response and suggesting the formula based method for plotting chart. However, I want to automate the process and develope a VBA code (will be either used as toolbar .xlsm or code in excel sheet) that will help me in plotting the bubble chart with a click of a button by selecting the input range.


The problem I am facing with the code is, the columns ranges from any value 3 to N (based on the available data e.g. 15, 20). Hence following challanges:
to calculate the number of columns (represents Y axis data),
to insert blank row in front of each Y Data series,
to manually insert series numbers in reverse order and
plotting bubble chart by selecting new ranges (as shown in output).

Hope I have provided more details for why I need a VBA code.

Please suggest simple code for above mentioned exercise.

A B C D E F G H I
2010 5 3 15 17.66666667 22.66666667 27.66666667 32.66666667 37.66666667 42.66666667
2011 8 9 12 13.66666667 15.66666667 17.66666667 19.66666667 21.66666667 23.66666667
2012 14 15 16 17 18 19 20 21 22
2013 18 34 11 14 10.5 74 3.5 45 43
2014 24 32 9 6.666666667 45 52 71 32 78
2015 27 36 15 14 31 27 43 32 21
2016 33 39 18 15 7.5 9 12 18 54


Best regards,
Sagar