Moving charts using a macro
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Moving charts using a macro

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've produced a macro that creates a chart but I can't get it to go where I want it on my spreadsheet. I need to produce another macro that puts it two rows under my Analysis Table (the lines in the table may alter but the chart mut always remain two rows underneath). The chart should also occupy 25 rows of the sheet and go inbetween columns A to D.

    Can anyone help???

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Frankie,

    Since you are adding rows to the worksheet, I assume you know the row number of the last row. This being the case, you can use the following code to position your chart two rows below this last row index (LastRow):

    Dim iRow As Integer
    iRow = LastRow + 2
    Dim C As ChartObject
    Set C = ActiveSheet.ChartObjects("Chart 3")
    C.Top = Cells(iRow, 1).Top
    C.Height = Cells(iRow + 25, 1).Top - C.Top
    C.Left = 0
    C.Width = [d1].Left - C.Left

    This code refers to the chart by name. If you haven't named your chart, or you aren't sure what its name is (my example assumed "Chart 3") select your chart using Ctrl-Click with the mouse, and look at the Name box just above the top-left cell on the worksheet.

    Also, from your description I assumed you wanted the chart to span columns A through C.


    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm a little bit stuck on the line that I have to enter the last row, it keeps coming up with an error. My last row is 34. Do I have to have LastRow in the code?

    Also what if the data changes, and there are more rows. Row 34 might not be the last row? Can this be made variable?

    HELP!

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've produced a macro that creates a chart but I can't get it to go where I want it on my spreadsheet. I need to produce another macro that puts it two rows under my Analysis Table (in row 34 but the lines in the table may alter but the chart must always remain two rows underneath). The chart should also occupy 25 rows of the sheet and go from columns A to D.

    Can anyone help???


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could insert the following at the beginning
    Range("a65533").End(xlUp).Select
    LastrRow = ActiveCell.Row()

  6. #6
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again Frankie,

    Yes, SAMS is exactly right. Thanks SAMS. If you put that in front of the code I posted that should work.

    It could be shortened a bit by incorporating it directly into my code:

    Dim iRow As Long
    iRow = [a65526].End(xlUp).Row + 2
    Dim C As ChartObject
    Set C = ActiveSheet.ChartObjects("Chart 3")
    C.Top = Cells(iRow, 1).Top
    C.Height = Cells(iRow + 25, 1).Top - C.Top
    C.Left = 0
    C.Width = [d1].Left - C.Left

    Happy charting.

    Damon

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you to all, it works perfectly.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com