need help creating "AutoSubtotal" toolbar button

[Paul Simon]

Similar to how the AutoSum toolbar button enters a =SUM formula into a
cell, I'm trying to create an "AutoSubtotal" button that would enter a
=SUBTOTAL formula into a cell. (I'm not trying to do Data>Subtotal. I
just want to enter a =SUBTOTAL formula into the cell.)

I thought this would work, but it doesn't. Any help would be greatly
appreciated.


Sub AutoSubtotal()
If ActiveCell.Offset(-1, 0).Formula = "" Then End
Else
ActiveCell.Formula = "=subtotal(9," & _
Range(ActiveCell.Offset(-1, 0).End(xlUp), _
ActiveCell.Offset(-1, 0)) & ")"
End If
End Sub


(I need the cell to actually contain a formula, so I can't use
WorksheetFunction.)

Many thanks,
Paul

 

[steve]

Paul,

Try this version:

Sub AutoSubtotal()
If ActiveCell.Offset(-1, 0).Formula = "" Then
Else
ActiveCell.Formula = "=subtotal(9," & _
ActiveCell.Offset(-1, 0).End(xlUp).Address _
& ":" & ActiveCell.Offset(-1, 0).Address & ")"
End If
End Sub

 

[Paul Simon]

Tom and Steve,

Both solutions work perfectly. Thank you both for responding to my
question and for taking the time to work out the code for me - I
appreciate it very much.

Many thanks,
Paul

 

[steve]

Paul,

Glad we could be of help!

--
sb

Tom and Steve,

Both solutions work perfectly. Thank you both for responding to my
question and for taking the time to work out the code for me - I
appreciate it very much.

Many thanks,
Paul


"steve" <[email protected]> wrote in message