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.
 

rohit9872

New Member
Joined
Oct 7, 2013
Messages
3
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
Joined
Nov 9, 2012
Messages
2,240
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
Joined
Oct 7, 2013
Messages
3
:) Thanks Teeroy, it works perfectly.
 

rajm11

New Member
Joined
Feb 18, 2016
Messages
19
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
Joined
Nov 9, 2012
Messages
2,240
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
Joined
Oct 27, 2005
Messages
18,185
Office Version
2013
Platform
Windows
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
Joined
Oct 27, 2005
Messages
18,185
Office Version
2013
Platform
Windows
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top