Moving charts using a macro

[Frankie needs HELP]

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???

 

[Damon Ostrander]

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.

 

[Frankie needs HELP]

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!

 

[Frankie needs HELP]

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???

 

[SamS]

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

 

[Damon Ostrander]

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

 

[Frankie needs HELP]

Thank you to all, it works perfectly.