Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Locate chart below last row of data

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,378
    Thanks
    207
    Thanked 829 Times in 762 Posts
    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.

    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
    Last edited by RetiredGeek; 2012-12-12 at 17:44.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    arjay13 (2012-12-13)

  4. #3
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •