SUM Formula using code

  • Thread starter Thread starter Sony
  • Start date Start date
S

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
 
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
 
Back
Top