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

  1. Only extend the ActiveChart
  2. 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.

 
                 Already Subscribed? Click HERE to log-in to the "Example Files" section

                 Already Subscribed? Click HERE to log-in to the "Example Files" section

 

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 :)