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

rohit9872

New Member
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

MrExcel MVP
Welcome to the Board!

Have you tried SubTotals?

HTH,

rohit9872

New Member
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

Well-known Member
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``````

rohit9872

New Member
Thanks Teeroy, it works perfectly.

rajm11

New Member
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.

Teeroy

Well-known Member
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

Well-known Member
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``````

Michael M

Well-known Member
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``````

1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...