How to let last row stay when data overflows using VBA

[Cicyhua]

Hi I need your help again.
How to keep my grand total stay at the bottom of my table even if data overflows? For example, when I have 20 rows of data, my grand total row stays at the 21th row, when I have 50 rows of data, my grand total row stays at the 51th without making any manual changes. I need a vba code or anything that can make this works. Thanks!!!!

 

[ranman256]

Do you mean like this where run a macro, and it summs the column?
(change J2 to the col. you need.)

Sub SumAll()
Dim r As Long
    Range("J2").Select
    Selection.End(xlDown).Select
    r = ActiveCell.Row
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-" & r & "]C:R[-1]C)"
End Sub

 

[Cicyhua]

Thank you very much! I need something like this. How do I write the code for multiple columns because I have to sum 5 columns.