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

T

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top