# Automatically Extending Excel Chart Series' Ranges For Next Year

/**It's That Time Of Year Again!**

For most of us, the month's of November and December are spent preparing our spreadsheets for next year. If you have historical graphs in your spreadsheets, adjusting your series ranges to include space for next year's numbers can be quite a pain (especially if you have a ton of graphs to adjust). This year I decided to bite the bullet and take some time to develop a VBA macro that can do these mind-numbing tasks for me and I will admit I threw a big fist pump into the air at my desk once I ran this code for the first time. Oh, how I love automation!!!

**The Problem**

Below is an example data set where I have my graphs setup for the years 2013-2014. I want to prepare them for next year and include space for 2015's numbers (when they get reported). With the code I wrote, I can simply run my macro, tell it how many cells I want to extend it to, and all the chart series in the worksheet are automatically extended!

**The VBA Code**

Sub Chart_Extender()

'PURPOSE: Extend horizontally all Chart Series in ActiveSheet by X number of columns (can decrease as well)

'SOURCE: www.TheSpreadsheetGuru.com

Dim Rng_Extension As Integer

Dim Series_Formula As String

Dim StartPoint As String

Dim EndPoint As String

Dim CommaSplit As Variant

Dim ColonSplit As Variant

Dim grph As ChartObject

Dim ser As Series

'Determine the length of the extension (in cells)

On Error GoTo BadEntry

Rng_Extension = InputBox( _

"How many cells do you want to extend your chart's series?", _

"Chart Extender")

On Error GoTo 0

'Loop Through Each chart in the ActiveSheet

For Each grph In ActiveSheet.ChartObjects

For Each ser In grph.Chart.SeriesCollection

'Test to make sure not XY Scatter Plot Series

If ser.ChartType <> 75 Then

'Get range of series

Series_Formula = ser.Formula

'X Axis Values

CommaSplit = Split(Series_Formula, ",") 'Delimit by comma

ColonSplit = Split(CommaSplit(2), ":") 'Delimit 3rd part by colon

StartPoint = ColonSplit(0) 'Starting Point of Range

EndPoint = ColonSplit(1) 'Current Ending Point Range

EndPoint = Range(EndPoint).Offset(0, Rng_Extension).Address 'Extended Ending Point Range

ser.Values = StartPoint & ":" & EndPoint 'Combine Start and End Point & Set Series = To It

'X Axis Labels

If CommaSplit(1) <> "" Then

ColonSplit = Split(CommaSplit(1), ":") 'Delimit 3rd part by colon

StartPoint = ColonSplit(0) 'Starting Point of Range

EndPoint = ColonSplit(1) 'Current Ending Point Range

EndPoint = Range(EndPoint).Offset(0, Rng_Extension).Address 'Extended Ending Point Range

ser.XValues = StartPoint & ":" & EndPoint 'Combine Start and End Point & Set Series = To It

End If

End If

Next ser

Next grph

'Completion Message

MsgBox "Your chart has been Extended by " & Rng_Extension & " positions."

Exit Sub

'Error Handling

BadEntry:

MsgBox "Your input must be a whole number, aborting", vbCritical, "Improper Entry"

End Sub

**How Does It Work?**

What the code does is loop through each chart series within a particular graph and then isolates the **Series ****Formula**. An example of what this formula might look like is shown below.

The code then takes this string and does a few delimited splits to single out the start and end cells of the two ranges highlighted in the above image (X-Axis and Data ranges).

The macro then uses the **Offset function** to expand or contract the chart range; essentially finding a new end point for the range. Once the new endpoint is found, the Series formula is then reassembled and given back to the chart!

**Different Scenarios**

I also went ahead and wrote code to handle two additional scenarios

- Only extend the ActiveChart
- Extend every chart in the entire ActiveWorkbook

If you would like to see how I wrote both of these macros and also test all three of them out, you can download the sample workbook I showed above in the animated GIF image.

As always, in order to download this example file you will need to be a subscriber of my free newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

**How Do I Modify This To Fit My Specific Needs?**

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: **My Guide to Getting the Solution to your Problems FAST!** In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, *you name it*!

I highly recommend that you check **this guide** out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)