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

rohit9872

New Member
Joined
Oct 7, 2013
Messages
3
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
If you want to insert the Formula from the macro you can use

Code:
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
 
Upvote 0
If you want to insert the Formula from the macro you can use

Code:
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.
 
Upvote 0
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?
 
Upvote 0
Maybe this
Code:
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
 
Upvote 0
Late edit
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top