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.


Need Your Help

ImportError admin module

django django-admin

I want to enter to administration application of my site.