# VBA: Reducing chart data range

I want to modify the code in this answer so that instead of increasing the range by one, it reduces reduces the range by one. Any ideas on how to do this?

Sub ChangeChartRange() Dim i As Integer, r As Integer, n As Integer, p1 As Integer, p2 As Integer, p3 As Integer Dim rng As Range Dim ax As Range 'Cycles through each series For n = 1 To ActiveChart.SeriesCollection.Count Step 1 r = 0 'Finds the current range of the series and the axis For i = 1 To Len(ActiveChart.SeriesCollection(n).Formula) Step 1 If Mid(ActiveChart.SeriesCollection(n).Formula, i, 1) = "," Then r = r + 1 If r = 1 Then p1 = i + 1 If r = 2 Then p2 = i If r = 3 Then p3 = i End If Next i 'Defines new range Set rng = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p2 + 1, p3 - p2 - 1)) Set rng = Range(rng, rng.Offset(0, 1)) 'Sets new range for each series ActiveChart.SeriesCollection(n).Values = rng 'Updates axis Set ax = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p1, p2 - p1)) Set ax = Range(ax, ax.Offset(0, 1)) ActiveChart.SeriesCollection(n).XValues = ax Next n End Sub

## Answers

I have replaced two lines in the original code in order to reduce the number of columns in a chart each time the macro runs.

Sub ChangeChartRange() Dim i As Integer, r As Integer, n As Integer, p1 As Integer, p2 As Integer, p3 As Integer Dim rng As Range Dim ax As Range 'Cycles through each series For n = 1 To ActiveChart.SeriesCollection.Count Step 1 r = 0 'Finds the current range of the series and the axis For i = 1 To Len(ActiveChart.SeriesCollection(n).Formula) Step 1 If Mid(ActiveChart.SeriesCollection(n).Formula, i, 1) = "," Then r = r + 1 If r = 1 Then p1 = i + 1 If r = 2 Then p2 = i If r = 3 Then p3 = i End If Next i 'Defines new range Set rng = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p2 + 1, p3 - p2 - 1)) Set rng = rng.Resize(rng.Rows.Count, rng.Columns.Count - 1) '~~> Replaced line 'Sets new range for each series ActiveChart.SeriesCollection(n).Values = rng 'Updates axis Set ax = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p1, p2 - p1)) Set ax = ax.Resize(ax.Rows.Count, ax.Columns.Count - 1) '~~> Replaced line ActiveChart.SeriesCollection(n).XValues = ax Next n End Sub

I think what you need to do is change

Set rng = Range(rng, rng.Offset(0, 1))

to

Set rng = Range(rng, rng.Offset(0, -1)).

**EDIT :** Try changing this equation around

Set rng = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p2 + 1, p3 - p2 - 1))

For example, you could try :

Set rng = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p2 - 1, p3 - p2 - 1))

or

Set rng = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p2 + 1, p3 + p2 - 1))

or

Set rng = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p2 + 1, p3 - p2 + 1))

And so on so forth...

You can use a free add-in on my site to adjust the chart series formula. It works like Find-Replace. Read the last row of data from the series formula, that's the change-from value, subtract one to get the change-to value. The tutorial is Change Series Formula – Improved Routines, and it starts with some VBA code if you want to try it yourself, but near the end is a link to the "Change Series Formula" add-in.