Excel Macro to Sum below varying column of data

[TXRH]

Hello everyone,

I have a report that list the total cost of the item in the row, in column O. I would like the macro to put a total of the items in the rows, below the last item in column O. I can get the macro to take me to cell where I need the total displayed, I just don't know how to make it total the row if the number of rows will vary each time the macro is ran.
The first row is always O3.
The recorder wrote:

ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"

Which would be fine if the number of rows were staying the same but they wont. I really need it to say

ActiveCell.FormulaR1C1 = "=SUM("O3":R[-1]C)"

But that doesn't work.

Thanks for any help!

 

[jmthompson]

untested

Sub MySum()
Dim lastRow as Long

lastRow = Range("A" & Rows.Count).End(xlUp).Row +1
Range("O" & lastRow).FormulaR1C1 = "=Sum(R3C:R[-1]C)"

End Sub