CODEWITHSUNDEEP
vbaexcel
TGS
TGS

Reputation: 1

How to create a sum function under last piece of column data with flexibility

Selection.End(xlDown).Select
Range("L108").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-106]C:R[-1]C)"
Range("L108").Select
Selection.Copy
Range("L108:N108").Select
Application.CutCopyMode = False
Selection.FillRight

need to add a sum function under column L, M, and N with ability to be able to adapt to other spreadsheets where number of rows will change so the sum will not be the same range

Upvotes: 0

Views: 61

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

The following will insert a formula below the last cell in column B:

Sub MakeColumnSum()
    Dim kolumn As String, where As Range
    kolumn = "B"
    Set where = Range(kolumn & Rows.Count).End(xlUp).Offset(1, 0)
    where.Formula = "=SUM(" & kolumn & "1:" & kolumn & where.Row - 1 & ")"
End Sub

Upvotes: 1

user4039065
user4039065

Reputation:

This will find the bottom value (row 107 from your sample code) and input a SUM formula in the next row (row 108 in your sample) for columns L, M and N.

dim lr as long
with worksheets("sheet1")
    lr = .cells(.rows.count, "L").end(xlup).row
    .cells(lr + 1, "L").resize(1, 3).formular1c1 = _
        "=sum(r2c:r" & lr & "c)"
end with

Upvotes: 1

Related Questions