CODEWITHSUNDEEP
vbaexcel
Nikitau
Nikitau

Reputation: 371

Updating chart with new column of data

I currently have data that I'm plotting into charts that looks like:

Period     0       1     2     3
Variable   2.1    2.5   2.8   3.0

I currently have VBA that expands the range of my charts by one column everytime I run it. However, I would like to know if there's a more efficient way to code it such that VBA calculates the very last column of my sheet that has data and plots the datapoints accordingly. The one problem with my current code is that if I accidentally run the VBA too many times, I will have blank cells plotted on my chart.

Sub ChangeChartRange()

    'Defines objects
    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
    Dim wks As Worksheet
    Dim cht As ChartObject

    Set CurrentSheet = ActiveSheet

    'For () loop statement that cycles through each worksheet
    For Each wks In ActiveWorkbook.Worksheets

    'Activates workbook
    wks.Activate

    'For () loop statement that cycles through each chart
    For Each cht In ActiveSheet.ChartObjects

        'Activates chart
        cht.Activate

        'Start counter at 0
        r = 0

        'Finds the current range of the series and the axis
        For i = 1 To Len(ActiveChart.SeriesCollection(1).Formula) Step 1
            If Mid(ActiveChart.SeriesCollection(1).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(1).Formula, p2 + 1, p3 - p2 - 1))
        Set rng = Range(rng, rng.Offset(0, 1))

        'Sets new range for each series
        ActiveChart.SeriesCollection(1).Values = rng

        'Updates axis
        Set ax = Range(Mid(ActiveChart.SeriesCollection(1).Formula, p1, p2 - p1))
        Set ax = Range(ax, ax.Offset(0, 1))
        ActiveChart.SeriesCollection(1).XValues = ax

    Next cht
    Next wks

End Sub

Help would be much appreciated! I'm still very new to VBA - I pulled this code somewhere online. Still trying my best to learn as I go along!

Upvotes: 0

Views: 345

Answers (1)

Mistella
Mistella

Reputation: 1728

If you don't have any blank cells in the middle of your data, and the next column to the right is blank, you can use the rng.End(xlToRight) to find the last cell with data in it. You can see in the code below, that I replaced the .Offset(0,1) with .End(xlToRight) for the new range and the axis.

       'Defines new range
        Set rng = Range(Mid(ActiveChart.SeriesCollection(1).Formula, p2 + 1, p3 - p2 - 1))
        Set rng = Range(rng, rng.End(xlToRight)) 'was Set rng = Range(rng, rng.Offset(0, 1))

        'Sets new range for each series
        ActiveChart.SeriesCollection(1).Values = rng

        'Updates axis
        Set ax = Range(Mid(ActiveChart.SeriesCollection(1).Formula, p1, p2 - p1))
        Set ax = Range(ax, ax.End(xlToRight)) 'was Set ax = Range(ax, ax.Offset(0, 1))
        ActiveChart.SeriesCollection(1).XValues = ax

Explanation of ActiveChart.SeriesCollection(1).Formula

In a graph I have, I extracted the SeriesCollection(1).Formula. It looks like this: "=SERIES('Jan 17'!$D$7:$D$8,'Jan 17'!$C$9:$C$33,'Jan 17'!$D$9:$D$33,1)" In this string the first range is the description, the second the axis, and the third the relevant data.

The following code steps through this string one character at a time. If the current character is a comma, r gets updated and the conditional values for p1,p2, and p3 get assigned when r reaches the correct value. p1 is the 1st character after the first comma (the beginning of the axis string). p2 is the location of the second comma (divider between axis and data). p3 is the location of the third comma (the 1st character after the data string).

For i = 1 To Len(ActiveChart.SeriesCollection(1).Formula) Step 1
    If Mid(ActiveChart.SeriesCollection(1).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

The next section of code extracts the section of the string starting at the data, for the length of the data. 'Jan 17'!$D$9:$D$33 This is converted to a range object, which is then extended.

'Defines new range
Set rng = Range(Mid(ActiveChart.SeriesCollection(1).Formula, p2 + 1, p3 - p2 - 1))
Set rng = Range(rng, rng.Offset(0, 1))

The following code extracts the section of the string starting at the beginning of the axis, for the length of the axis. 'Jan 17'!$C$9:$C$33 This is converted to a range object, which is then extended and reassigned to the axis values.

'Updates axis
Set ax = Range(Mid(ActiveChart.SeriesCollection(1).Formula, p1, p2 - p1))
Set ax = Range(ax, ax.Offset(0, 1))
ActiveChart.SeriesCollection(1).XValues = ax

Upvotes: 1

Related Questions