Automatic update of chart using offset or vba but with hidden columns

[saadalrahman]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Month[/TD]
[TD]Jan'13[/TD]
[TD]Feb'13[/TD]
[TD]Mar'13[/TD]
[TD]Jan'14[/TD]
[TD]Feb'14[/TD]
[TD]Mar'14[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Assets[/TD]
[TD]32[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]29[/TD]
[TD]29[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Equity[/TD]
[TD]4.3[/TD]
[TD]4.2[/TD]
[TD]4.3[/TD]
[TD]4.1[/TD]
[TD]4.3[/TD]
[TD]4.3[/TD]
[/TR]
</tbody>[/TABLE]

I have a table as above and have created a bar chart that compares 3 months of 2013 with 3 months of 2014. The values of Apr'13 thru Dec'13 are in hidden columns F thru N. I have a macro setup so that the value for Apr'14 are automatically entered into the table and the relevant columns are hidden/unhidden so that the corresponding 3 months are shown for last year and this year. So for example, once the data for Apr'14 is entered the Jan'13 and Jan'14 columns are hidden and the Apr'13 column (Column F) is unhidden.

I have been trying unsucessfully to have the chart update automatically using only the visible columns. I have tried the offset range option for the chart data and it always includes the hidden columns as well so I am not sure how to set it to only use Visible columns. I would appreciate any help whether with the offset range option or vba code.

Thanks.

 

[saadalrahman]

Nvm. I figured it out by using Range Series for my chart and extending the data to include everything.