VBA to Readjust Chart Series Range

[Chris Macro]

I have a bunch of graphs with multiple series that I need to adjust the range width that they are picking up. For example, Series1 might read data from "A1:C1" one month and then the next it might read "A1:M1". I have the looping down to get me to the Chart Series level however I am not sure how to extract the range data and manipulate it so that I can change the range (Not sure if it is possible to set a Range Variable equal to the Series range). Note: that the starting point of the range will never move, just the endpoint. Also Note: that each series has a different starting point (ie Series1 may start in column A while Series2 may start in Column Z). Any thoughts would be greatly appreciated!

CODE:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Adjust_GraphSeries_Width()<br><br><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> Grph <SPAN style="color:#00007F">As</SPAN> ChartObject<br><SPAN style="color:#00007F">Dim</SPAN> Ser <SPAN style="color:#00007F">As</SPAN> Series<br><SPAN style="color:#00007F">Dim</SPAN> Rng_Extenstion <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br>Rng_extension = 7<br><br><SPAN style="color:#00007F">Set</SPAN> sht = ThisWorkbook.ActiveSheet<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Grph <SPAN style="color:#00007F">In</SPAN> sht.ChartObjects<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Ser <SPAN style="color:#00007F">In</SPAN> Grph.Chart.SeriesCollection<br>        x = Ser.Name<br>        <br>        <SPAN style="color:#00007F">If</SPAN> Ser.ChartType <> 75 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Test to make sure not XY Scatter Series</SPAN><br>           Rng = Ser.Formula <SPAN style="color:#007F00">'Get range of series</SPAN><br>           <SPAN style="color:#007F00">'then need to get starting point of range and offset range</SPAN><br>           <SPAN style="color:#007F00">'by Rng_Extention variable number</SPAN><br>                        <br>            x = Rng.Address<br>            <br>            Ser.Values = x<br>        <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>    <SPAN style="color:#00007F">Next</SPAN> Ser<br><SPAN style="color:#00007F">Next</SPAN> Grph<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>