Subtotal Function

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
I constantly have the need for using the subtotal function and am wondering if their is an easier way than just typing in the subtotal argument for each time I need it. For instance the sum function has a quick button on the tool bar... is their a way to insert a quick function for subtotal that would save time?

Any advice would be great!

Thanks in advance
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
I have this macro that does subtotals formula at the place of your cursor. So put this code into a macro file or your personal workbook, then add a button on your toolbar for this macro

Code:
Sub AutoSubtotal_Formula()
    Dim x As String
    Dim y As String
    
        x = ActiveCell.Offset(-1, 0).End(xlUp).Address
        y = ActiveCell.Offset(-1, 0).Address
        
        ActiveCell = "=SUBTOTAL(9," & x & ":" & y & ")"
        
End Sub
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
This is nice!

A couple of questions:

1] It this would basically replicate the sum function on the toolbar?
2] How can I add a button on my toolbar for this function?

Thank you for all your help!

Sean
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
to add a button you goto the
View - Toolbars - Customize
select the Commands tab and scroll down to find in the categories: Macros
there is a custom button
Drag the custom button onto your toolbar, right click on that button now on your toolbar and click, assign macro. The macros selection window will be displayed, click on the auto_subtotal macro.
Close the customize window.

Now when you click on that button it will run that macro.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141

ADVERTISEMENT

Thank you very much! This is perfect just what I needed

Do you know if I can make is available to any workbook without manually putting in the code for each workbook I desire it's use for?

If I wanted to use it in other external workbooks I would need the workbook with the Macro to be Open!

Thanks again!

Cheers
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
the way I do my macros, is I have a file where I keep most all of them.
When you create a macro button and point to the macro name the macro button knows the file that has the macro code. Then when you close your "all" macros file and click on the icon button that will automatically open the file where the macro is kept.

The other option is saving your general macros to the personal workbook. I don't prefer this because it conflicts with other macros - I'm sure that others will tell you different reasons why to use personal workbook. I feel it's just a preference.
 

Forum statistics

Threads
1,136,370
Messages
5,675,365
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top