Specifiy Chart Data Range

[Andy]

I have a data source which varies in length, and I need to specify the data
range for the chart which displays this data. At present I have:
ActiveChart.SetSourceData Source:=Sheets("Site
Data").Range("C2:C500,G2:H500" _
), PlotBy:=xlColumns

Instead of going dwon to line 500 I want it to go down to a line number set
by the variable "lastrow".

Thanks,

Andy

 

[Peter T]

Hi Andy,

You could build the address string to include your variable last row number,
or use the Cells method

Dim rSource As Range
Dim lastRow As Long

lastRow = 500

With ActiveWorkbook.Worksheets("Sheet1")
Set rSource = .Range(.Cells(2, 3), .Cells(lastRow, 8))
End With

'Note the dots in the above to qualify cells to the sheet (required if not
on active sheet)

ActiveChart.SetSourceData Source:=rSource, PlotBy:=xlColumns

Regards,
Peter T