Update chart range to the end row

Question

I would like to update the ranges in an existing chart with a macro. My data will always start on the same row but will continue to grow as a new data is added (e.g. $AK$15, $AK$16). I know this can be done by using dynamic charts but I've been asked to do it via VBA. My code is as follows:

ThisWorkbook.Worksheets("Overview - Tier 1").Activate
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.SetSourceData Source:=Worksheets("Graph Data - Tier 1").Range("$AI$9:$AK$14")

I tried to modify my code as below but it doesn't work (I have a Runtime error 1004: Application-Definied or object-definnied error)

ThisWorkbook.Worksheets("Overview").Activate
Dim LastRow As Long
LastRow = Worksheets("Graph Data").Range("$AI$9" & Rows.Count)
ThisWorkbook.Worksheets("Overview").Activate
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.SetSourceData Source:=Worksheets("Graph Data").Range("$AI$9::$AK$14 & LastRow")

For start, change "$AI$9::$AK$14 & LastRow" with "$AI$9:$AK$" & LastRow. However you have some errors on your code. For example, you declared LastRow as long when you defined it later as a range of cells. — Pspl

Error 1004 Error 1004 · Accepted Answer · 2019-02-25T12:49:21

This may helps:

Option Explicit

Sub test()

    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Graph Data")
        LastRow = .Cells(.Rows.Count, "AI").End(xlUp).Row
    End With

    With ThisWorkbook.Worksheets("Overview")
        .ChartObjects("Chart 7").Chart.SetSourceData Source:=Worksheets("Graph Data").Range("AI9:AK" & LastRow)
    End With

End Sub

Update chart range to the end row

1 Answers