CODEWITHSUNDEEP
excelvba
Maha
Maha

Reputation: 25

Update Chart range to end Row

I have created the below macro its working fine .. I wants to pick value to end Row dynamically instead $A$3:$A$328 .. something like Range("A3" & Rows.Count).End(xlUp).Row

Can anyone help me please 

  Private Sub CommandButton1_Click()

    Dim arrCharts, cht
    Dim wb As Worksheet
    Dim wbTarget As Workbook
    Dim sh As Worksheet


    Set wbTarget = Workbooks.Open(Filename:="C:\Users\mahather\Desktop\Report\Summary Report.xlsx")
    Set wb = wbTarget.Worksheets("Graph")
    Set sh = wbTarget.Worksheets("Summary")


    arrCharts = Array("Chart 2", "Chart 5")

    For Each cht In arrCharts
        With wb.ChartObjects(cht).Chart
            .SeriesCollection(1).XValues = "='Summary'!$A$3:$A$328"
        End With
    Next cht


    End Sub

Upvotes: 0

Views: 284

Answers (1)

Ko Nayaki
Ko Nayaki

Reputation: 86

You could use the below code, 

Dim LastRow As Long
 LastRow = ThisWorkbook.Sheets("Summary").UsedRange.Rows(ThisWorkbook.Sheets("Summary").UsedRange.Rows.Count).Row

and then edit the below line under for each

  .SeriesCollection(1).XValues = "='Summary'!$A$3:$A$" & LastRow

Let me know if this helps.

Upvotes: 1

Related Questions