Change selection range based on last column and row

[ExcelNoob222]

Hi,

I have the below code that sets my chart range to the last row. However my data will not always go to column J, it could change to M, N etc. How do I adjust the code so that it grabs the last row and last column?

Thanks!

LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Seasonality").Range("A2:J" & LastRow)

 

[Joe4]

You could use SpecialCells, Last Cell to get to the intersection of the last row/column, i.e.

Dim LastCell as String
LastCell = Range("A1").SpecialCells(xlLastCell).Address
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Seasonality").Range("A2:" & LastCell)

 

[ExcelNoob222]

You could use SpecialCells, Last Cell to get to the intersection of the last row/column, i.e.

Dim LastCell as String
LastCell = Range("A1").SpecialCells(xlLastCell).Address
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Seasonality").Range("A2:" & LastCell)

Hi,

Sorry I did not get back to you sooner! That worked. Thank you very much.

 

[Joe4]

You are welcome.
Glad to hear that it worked for you!