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