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

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

On 9 Jan 2004 06:22:42 -0800, (Tim Richardson) wrote:

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