I want my macro to add SUM formula in the blank cell below last row

[rohit9872]

I pull this pending revenue report everyday and the column L contains the amount due for each customer. I want my macro to add the total of those amounts in the next blank cell below after the column ends. Suppose the data ranges from L2:L13 then L14 should have the formula =SUM(L2:L13), if ranges from L2:L25 then L26 should have the formula =SUM(L2:L26) and so on. Thanks.

 

[Smitty]

Welcome to the Board!

Have you tried SubTotals?

HTH,

 

[rohit9872]

No I don't want SubTotals. I just want the macro to add all the numbers of the L column in the first blank cell after the LastRow with data in L.

 

[Teeroy]

If you want to insert the Formula from the macro you can use

Dim lLastRow As Long
With Sheets("Sheet1") 'put your sheet name here
    lLastRow = .Range("L" & .Rows.Count).End(xlUp).Row
    .Range("L" & lLastRow + 1).Formula = "=Sum(L2:L" & lLastRow & ")"
End With

 

[rohit9872]

Thanks Teeroy, it works perfectly.

 

[rajm11]

If you want to insert the Formula from the macro you can use

Dim lLastRow As Long
With Sheets("Sheet1") 'put your sheet name here
    lLastRow = .Range("L" & .Rows.Count).End(xlUp).Row
    .Range("L" & lLastRow + 1).Formula = "=Sum(L2:L" & lLastRow & ")"
End With

Hello, thanks for the solution it does work for me but I wanted to extend this. I want the macro to add the formula in all columns of row. I mean instead of Range "L" in above example can i make row a to z .
My limitation stems from the fact that I dont know how to address the range and keep it in memory.

 

[Teeroy]

Hello, thanks for the solution it does work for me but I wanted to extend this. I want the macro to add the formula in all columns of row. I mean instead of Range "L" in above example can i make row a to z .
My limitation stems from the fact that I dont know how to address the range and keep it in memory.

How do you select the row that you want to place the formula in?

 

[Michael M]

Maybe this

Sub MM1()
Dim lr As Long, lc As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(Columns.Count, 1).End(xlToLeft).Column
With Sheets("Sheet1") 'put your sheet name here
    .Range("A" & lr + 1).Formula = "=Sum(A2:A" & lr & ")"
    .Range("A" & lr + 1).AutoFill Destination:=Range("A" & lr + 1 & ":F" & lr + 1)
End With
End Sub

 

[Michael M]

Late edit

Sub MM1()
Dim lr As Long, lc As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
With ActiveSheet
.Range(Cells(lr + 1, 1), Cells(lr + 1, lc)).Formula = "=Sum(A2:A" & lr & ")"
End With
End Sub