macro for variable multi series in chart

Optic

New Member
Joined
Dec 6, 2012
Messages
17
I need to chart some data, but as the data get's updated each week so the number of series change, i've done the offset with names with one series and this is fine, but i need a macro that will look at the data and add multiple series to a chart, this is due to the chart data range does not accept names. The data is not in a chart as it is generated from formulas.


</SPAN>Week Ending 29/03/13</SPAN>Week Ending 22/03/13</SPAN>Week Ending 15/03/13</SPAN>Week Ending 08/03/13</SPAN>
MOD 999</SPAN>14.25</SPAN>19</SPAN>34.5</SPAN>31.5</SPAN>
MOD 111</SPAN>0.75</SPAN>0</SPAN>3.75</SPAN>10</SPAN>
MOD 4450</SPAN>0</SPAN>4</SPAN>0</SPAN>
MOD 8970</SPAN>3.5</SPAN>1.5</SPAN>2</SPAN>
MOD 6580.75</SPAN>0</SPAN>0</SPAN>0</SPAN>
MOD 7483.75</SPAN>0</SPAN>0</SPAN>0</SPAN>
MOD 1450</SPAN>1.5</SPAN>1.5</SPAN>2</SPAN>
MOD 1090</SPAN>1.5</SPAN>1.5</SPAN>2</SPAN>
MOD 1070</SPAN>0</SPAN>0</SPAN>11.75</SPAN>
MOD 1050</SPAN>1.5</SPAN>1.5</SPAN>2</SPAN>
MOD 88716.25</SPAN>0</SPAN>0</SPAN>0</SPAN>
MOD 10068</SPAN>124</SPAN>69.75</SPAN>42</SPAN>
MOD 1060</SPAN>0</SPAN>0</SPAN>1</SPAN>
MOD 390</SPAN>5</SPAN>24.75</SPAN>48.5</SPAN>
MOD 8992</SPAN>8</SPAN>2</SPAN>5.5</SPAN>
MOD 8999</SPAN>0</SPAN>0</SPAN>10.75</SPAN>0</SPAN>
MOD 4510.75</SPAN>18.5</SPAN>7</SPAN>0</SPAN>
MOD 881</SPAN>2</SPAN>0</SPAN>0</SPAN>
MOD 1011</SPAN>0</SPAN>0</SPAN>0</SPAN>
MOD 9001</SPAN>0</SPAN>0</SPAN>4</SPAN>5.5</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>
0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>


I need a chart that has x and week ending and y hours, macro's are all new to me,

thanks in advance
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Ive formatted the chart how i want it to be shown, i don't want to use macro's but i don't want to chart all the data and all the empty N/A at the bottom and it adds spaces in to the chart and does not look good, with one series i've done the offset function with a named range, but can not do this to get around the issue i only want a series that contains data,


https://www.dropbox.com/s/im2kmqty9u6lgze/test1.xls
 
Upvote 0
Hide the columns and rows you don't want to plot
 
Upvote 0
Hiding would be a manual operation that i don't want to do, this data is in a sheet that has data spanning A1:CJ612, all doing various automated actions to filter out and arrange the data so a chart dashboard page can display it. so far i've got everything to be automated and don't want this issue to be the first thats not, i'll carrying on googling a solution, ive found some but i need to know more about vba to adapt it.
 
Upvote 0

Forum statistics

Threads
1,206,944
Messages
6,075,778
Members
446,154
Latest member
Dirk46

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top