SUM Formula using code

[Sony]

Hi All,

How do you put a SUM Formula using VBA code in every end of rows where
data rows are vary from time to time.

I know it's : Range().Formula ="=Sum(Range)"

But my point is, how can you anticipate data rows that vary from time
to time and always put the SUM formula in the end of every data rows.

Thanks in advance.

sony

 

[Peter Beach]

Hi Sony,

How about something like:

Sub InsertTotals()
Dim i As Long
Dim r As Range
Dim WS As Worksheet

Set WS = ThisWorkbook.Worksheets(1)

For i = 1 To 256
Set r = WS.Cells(65536, i).End(xlUp)
If r.Row = 1 Then Exit For ' Assume no more data
Set r = r.Offset(1, 0)
r.Formula = "=SUM(R1C" & i & ":" & r.Offset(-1, 0).Address(, , xlR1C1) &
")"
r.Font.Bold = True
Next i
End Sub

HTH

Peter Beach