Home dynamic rows and columns in VBA Chart

# dynamic rows and columns in VBA Chart

user4081
1#
user4081 Published in September 19, 2018, 7:13 pm

so i am trying to write some VBA that can look at "sheet2" and determine how many rows and columns it needs to make in its line chart

i'm having difficulty in declaring range of .SetSourceData in terms of variable i need it to start at cell(1,4) and go to Cell(LastRow, LastColumn). when i try i get a error

'method 2 Dim LastRow As Long Dim LastColumn As Long Dim rng2 As Range Dim ShName As String With ActiveSheet

LastRow = ThisWorkbook.Sheets("Sheet2").Range("G" & .Rows.Count).End(xlUp).Row
LastColumn = ThisWorkbook.Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
Set rng2 = ThisWorkbook.Sheets("Sheet2").Range(.Sheets("Sheet2").Cells(2, 4), .Sheets("Sheet2").Cells(LastRow, LastColumn))


' ThisWorkbook.Sheets("Sheet2").Range("R2").Value = rng2

ShName = .Name
End With

With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=rng2
End With