Locate chart below last row of data

Topic

New Reply

Hi
I have a macro that creats a chart at a specific location on my spreadsheet.

ActiveSheet.Shapes.AddChart(xlXYScatter, Left:=Range(“C52”).Left, Top:=Range(“c52”).Top, Width:=Range(“c52:I52”).Width, Height:=Range(“c52:c68”).Height).Select

It allows for about 20 rows of data above it. What I would like to do is to automatically locate the chart below the last row of data no matter how many rows of data I have so that the chart will never cover any of the data rows. I tried to xldown to the last row and then use RC references from that bottom cell but I have not been sucessful.

Any thoughts?
Thanks much
Arjay

Reply | Quote

Replies

Arjay,

This function will return the last used row, no matter what version of Excel you use.

Code:

Function lLastUsedRow() As Long
   
   Dim lTotalRows   As Long
   
   lTotalRows = Rows.Count
   Application.Goto Cells(lTotalRows, 3)
   Selection.End(xlUp).Select
   lLastUsedRow = ActiveCell.Row

End Function

Just call it before your chart command and use it to adjust your parameters as necessary. :cheers:

Note: I used Col C {3} since that was aparently the 1st row of your data. You can change that number as appropriate or you could adjust the function call to pass the row to use e.g.

Code:

Function lLastUsedRow(lDataCol as Long) As Long
   
   Dim lTotalRows   As Long
   
   lTotalRows = Rows.Count
   Application.Goto Cells(lTotalRows, lDataCol)
   Selection.End(xlUp).Select
   lLastUsedRow = ActiveCell.Row

End Function

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RetiredGeek
Thanks for the code. It took me awhile to determine the best way to implement it in my macro. Once done, it works like a charm. The main problem was that I had to abandon using cell references in the addchart stament if favor of using points. Once I did that it all came together.
Thanks Again
Arjay

Reply | Quote