autosum with Macros

[chris]

I have a data list that changes on a daily basis. I'd
like to create a macro that places a new autosum at the
bottom of my existing data... and have the sum be correct
no matter how the list grows. I do NOT want to put a sum
function that stays on the bottom of the list, and updates
based on a growing list of records...

THe macro needs to REMOVE the formulas and then insert a
brand new SUM below my current list.

Any ideas? (I've tried relative code with range names,
etc.... Still trying, but would appreciate any help. Thx!)

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("sheet1")
With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'delete or just overwrite?
.Rows(LastRow).Delete
'put something into that row
.Cells(LastRow, "A").Resize(1, 10).Formula = "=rand()"
'add the sum again.
.Cells(LastRow + 1, "A").Resize(1, 10).FormulaR1C1 _
= "=sum(R1C:R[-1]C)"
End With

End Sub

I just deleted the lastrow, added a bunch of random numbers to that row (10
columns) and then put the formula in the next row.

I do have a suggestion that may make your life easier.

Put your headers in Row 2. But put your totals in row 1. If you freeze panes,
you'll always be able to see them.

And if you use =subtotal()'s in row 1, you can apply data|filter|autofilter and
see the nice subtotals.

And it makes your original problem pretty much go away.