CODEWITHSUNDEEP
excelvbacharts
wglass21
wglass21

Reputation: 3

Dynamic chart with VBA

I'm writing an Excel macro that creates charts with dynamic ranges (i.e. that update automatically whenever a new row of data is added - https://trumpexcel.com/wp-content/uploads/2017/08/Dynamic-Chart-Range-in-Excel-Demo.gif). For the example I'm testing, my data is in columns A and B.

I do know this can be done through either tables or defined ranges, but unfortunately neither of those work for this particular project.

The code I'm currently using (two different versions) create the graph fine without me explicitly defining the range. However, when I add a new row of data, the graphs do not update.

Sub AddGraphs()
    'Set the dynamic ranges
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column

    'Create the chart
    Charts.Add
    With ActiveChart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Range(Cells(1, 1), Cells(lr, LC))
        .Location xlLocationAsObject, "Sheet1"
    End With

    'Format chart and set location
    With ActiveChart
        .Parent.Top = Cells(1, LC + 3).Top
        .Parent.Left = Cells(1, LC + 3).Left
        .HasLegend = False
    End With
End Sub

'Alternative code

Sub Test()
    Dim LastRow As Long
    Dim Rng1 As Range
    Dim rng2 As Range
    Dim ShName As String
    With ActiveSheet
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set Rng1 = .Range("A2:A" & LastRow & ", B2:B" & LastRow)
        ShName = .Name
    End With
    Charts.Add
    With ActiveChart
        .ChartType = xlLine
        .SetSourceData Source:=Rng1

        .Location Where:=xlLocationAsObject, Name:=ShName
    End With
End Sub

Upvotes: 0

Views: 3979

Answers (2)

Cyril
Cyril

Reputation: 6829

I used Shapes to create the chart only on your activesheet, not create a chart-sheet.

Option Explicit

Sub AddGraphs()
    Dim lr As Long, lc As Long, ch As ChartObject
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    ActiveSheet.Shapes.AddChart.Name = "Cat" '<--- Used Shapes to keep this on the sheet only
    ActiveSheet.ChartObjects("Cat").Select '<--- Naming the Chart to later call and use in Change_Event
    With ActiveChart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Range(Cells(1, 1), Cells(lr, lc))
        .Location xlLocationAsObject, "Sheet1"
        .Parent.Top = Cells(1, lc + 3).Top
        .Parent.Left = Cells(1, lc + 3).Left
        .HasLegend = False
    End With
End Sub

Now, you have a chart made that you can use in the future. You want your change event to be relevant to updating the data series only, like:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Cells(Rows.Count, 2).End(xlUp).Row = Cells(Rows.Count, 1).End(xlUp).Row Then Exit Sub
    ChartObjects("Cat").Select '<--- Using named shape/chart
    With ActiveChart
        Dim lr As Long, lc As Long
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        lc = Cells(1, Columns.Count).End(xlToLeft).Column
        .SetSourceData Source:=Range(Cells(1, 1), Cells(lr, lc))
    End With
End Sub

Edit2: Redid post to fix a couple other things to make this work.

Upvotes: 0

wglass21
wglass21

Reputation: 3

Thank you @Cyril for the idea!

The code now updates as follows:

Option Explicit 'Excel worksheet change event Range A1 to B50
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:B50")) Is Nothing Then
            'Set the dynamic ranges

        Dim lr As Integer
        Dim lc As Integer

        lr = Cells(Rows.Count, 1).End(xlUp).Row
        lc = Cells(1, Columns.Count).End(xlToLeft).Column

    'Create the chart
        Charts.Add
        With ActiveChart
            .ChartType = xlColumnClustered
            .SetSourceData Source:=Range(Cells(1, 1), Cells(lr, lc))
            .Location xlLocationAsObject, "Sheet1"
        End With

    'Format chart and set location
        With ActiveChart
            .Parent.Top = Cells(1, lc + 3).Top
            .Parent.Left = Cells(1, lc + 3).Left
            .HasLegend = False
        End With
    End If
End Sub

This works great, except it creates a new chart every time the macro runs, and I would like to be updating the existing chart - would appreciate any help!

Upvotes: 0

Related Questions