VBA: Reducing chart data range

Question

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

Use John Walkenbachs class module approach. The code above failed on my chart type. — brettdj
– brettdj, Commented Jan 10, 2014 at 5:17
You just reposted the code from that other answer... Have you actually tried anything? e.g. try to understand what it does, and have a go at modifying it yourself? — Jean-François Corbett
– Jean-François Corbett, Commented Jan 10, 2014 at 7:34
Yes, I made effort to modify the code to reduce the range by 1 column. I'll be more specific in documenting same in the future. Lesson learned. The original coder provided the answer below. — Bryan
– Bryan, Commented Feb 18, 2014 at 21:05

Bernard Saucier · Accepted Answer · 2014-01-10 19:39:17Z

1

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...

edited Jan 10, 2014 at 19:39

answered Jan 10, 2014 at 2:25

Bernard Saucier

2,2601 gold badge21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bryan Over a year ago

Thanks Bernard. I tried your recommendation but it didn't work. Though it seems it should. Continuing to work it. Thanks for responding.

Netloh · Accepted Answer · 2014-02-18 20:23:34Z

0

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

answered Feb 18, 2014 at 20:23

Netloh

4,3784 gold badges27 silver badges38 bronze badges

1 Comment

Bryan Over a year ago

Soren, I tried the code and it worked perfectly. I'm going to expand its use to 40+ excel charts. Thanks.

Jon Peltier · Accepted Answer · 2014-01-17 19:43:57Z

0

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.

answered Jan 17, 2014 at 19:43

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

Collectives™ on Stack Overflow

VBA: Reducing chart data range

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related