Excel - vba code to identify last active cell in sheet and subtotal.

I would like to build a macro that will go to the last active cell (the row could change but the column should not) in a sheet named 'Sales Figures'.  From that cell, I would like to move down 1 space and then subtotal all the rows above.  Is this possible?
vsllcAsked:
Who is Participating?
 
dlmilleCommented:
A slight modification to focus on the Sheet in question, "Sales Figures":

Either use the same code, but with a:

Sheets("Sales Figures").activate - to ensure you're focused on the correct sheet, or make the following changes:


Sub subTotal()
Dim rng As Range
Dim myCol As Long
Dim buildRng As Range

    Set wks = Sheets("Sales Figures")
    myCol = 5 'you set this to the column you're concerned with

    Set rng = wks.Cells(Rows.Count, myCol).End(xlUp) 'this will set the range to the last cell "with data" in the column you specify, looking from the bottom, upward
    Set buildRng = Range(wks.Cells(1, myCol), rng)

    rng.Offset(1, 0).Formula = "=Sum(" & buildRng.Address & ")"
   
    rng.Offset(1, 0).Value = rng.Offset(1, 0).Value 'to clear the formula
   
End Sub

Dave
FindLastInsertAndSum-r2.xls
0
 
dlmilleCommented:
Sure:

Sub subTotal()
Dim rng As Range
Dim myCol As Long
Dim buildRng As Range

    myCol = 5 'you set this to the column you're concerned with

    Set rng = Cells(Rows.Count, myCol).End(xlUp) 'this will set the range to the last cell "with data" in the column you specify, looking from the bottom, upward
    Set buildRng = Range(Cells(1, myCol), rng)

    rng.Offset(1, 0).Formula = "=Sum(" & buildRng.Address & ")"
   
    rng.Offset(1, 0).Value = rng.Offset(1, 0).Value 'to clear the formula
   
End Sub


See attached,

Enjoy!

Dave

FindLastInsertAndSum-r1.xls
0
 
dlmilleCommented:
PS - to make this a subroutine, where you pass the Column to sum, you can do this:

 
Sub subTotal(myCol As Long)
Dim rng As Range
'Dim myCol As Long
Dim buildRng As Range

    Set wks = Sheets("Sales Figures")
    'myCol = 5 'you set this to the column you're concerned with

    Set rng = wks.Cells(Rows.Count, myCol).End(xlUp) 'this will set the range to the last cell "with data" in the column you specify, looking from the bottom, upward
    Set buildRng = Range(wks.Cells(1, myCol), rng)

    rng.Offset(1, 0).Formula = "=Sum(" & buildRng.Address & ")"
    
    rng.Offset(1, 0).Value = rng.Offset(1, 0).Value 'to clear the formula
    
End Sub
Sub test()

    Call subTotal(5)
End Sub

Open in new window


Enjoy!

Dave
0
 
vsllcAuthor Commented:
Thanks a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.