• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code for sum total with filter

V

Veeru106

Member
Hi,

i have one code which can find last row and also apply sum total formula at end

but now i have certain filter cretaria and then i need to apply sum at end.

Can you please look and make some adjustment so that it can sum at the end with filter applied. thanks
Code: 
Sub addtwo()
Dim LastRow As Long
Sheets("FY-2017").Select
Range("S2").Select
LastRow = Cells(Cells.Rows.Count, "S").End(xlUp).Row + 1
Range("S" & LastRow).Formula = "=sum(S2:S" & LastRow - 1 & ")"
End Sub
 
Veeru106
Formula
... change that formula to Your Sum-formula
or
BUT?
... every time, You'll run that Your code it'll add that formula - okay?
( after ten times ... there will be ten formulas - okay? )
 
ok so what would be the best way to deal it.
can you modify the above code and paste full code here , keeping in mind what you said above. thanks
 
Veeru106
I would use that 'Sum' in the 1st row
then no matter how many new rows will come.
... if You have already something there (S1)
... then You'll know what to do!
Code: 
Sub addtwo()
    Dim LastRow AsLong
    With Sheets("FY-2017")
        LastRow = .Cells(.Cells.Rows.Count, "S").End(xlUp).Row
        .Range("S1").Formula ="=aggregate(9,7,S2:S" & LastRow & ")"
    End With
EndSub
 

Hi !

So sum a filtered range you can also use
SUBTOTAL Excel worksheet function even in VBA …
​
 
Veeru106
You should upload a file to get an idea what You haven't done.
Ex what do You have in cell[S1] or cell[S2]?
For me, with You information which You have given ... it worked!
 
Hi !

A space is just lacking in the Dim codeline
as he must solve himself just comparing with its initial post ! :rolleyes:​
 
Veeru106
Two people gave a hint ... Dim LastRow As Long
... and there were same challenge ... hmm?
I try with photo ...
Screen Shot 2018-02-28 at 12.30.12.png
Plus:
EndSub ... also need to be written End Sub

Plus: as I tried to write in #6 Reply
I would use that 'Sum' in the 1st row
then no matter how many new rows will come.
... if You have already something there (S1)
... then You'll know what to do!
 
Did You change also
Dim LastRow As Long
As in this file?
 

Attachments

  • Book1.xlsx.xlsm
    13.5 KB · Views: 0
Ohh yes i changed it to Dim LastRow as Long..now it is working but it sum get pasted in Col. F1 at start, where it should be at bottom..
 
as Veeru106 wrote in #5 Reply
ok so what would be the best way to deal it.
can you modify the above code and paste full code here ,
keeping in mind what you said above. thanks
 
You must log in or register to reply here.
Back
Top