Change Autosum to use subtotal(9,....) ?

[Tim Richardson]

I want to make the Autosum button use subtotal(9, ..) rather than sum.
I see in this group many messages telling how to use Autosum
functionality via VBA to select the autosum range, and then substitute
a new formula in the activecell.

just curious if there is another way for this specific requirement. I
thought this might be a common requirement but my searches didn't show
it as such.

(Excel 2000)

regards,
Tim

 

[Gord Dibben]

Tim

Don't know how to add the SUBTOTAL Function to the AutoSum button, but you
could insert a new button on your toolbar and assign this code to it.

Sub SUBTOTAL_Range()
Set rng = Selection
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Formula = "=Subtotal(9," & rng.Address & ")"
End Sub

Gord Dibben Excel MVP