dynamic sum
 

I have just had to deal with the same issue and this is what I did

Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
i = Selection.Rows.Count
ActiveSheet.Range("g1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & i & "]C:R[-1]C)"



"E" wrote in message
...
I have a spreadsheet that recieves data from MS Access. The number of rows
and columns changes from month to month. I want to run the totals of each
row. The totals will appear at the end of row. I've managed to get part
of
it. I can't seem to get the dynamic part of the sum. In other words the
number of rows is hard coded. Here is my code:

Dim test As String
Dim Test2 As String
Dim lastrow As String

lastrow = ActiveSheet.UsedRange.Rows.Count
Test2 = ActiveSheet.UsedRange.Columns.Count
test = "B" & lastrow

Range("B2:" & test).Select
Range("B" & (lastrow + 1)).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-10]C)"


End Sub