SUBTOTAL Macro

Excel Featured by Jeff Lenning

The SUBTOTAL is a wonderful alternative to the SUM function, and this post shows how to set up a shortcut button that inserts the SUBTOTAL function. Our new shortcut button will be similar to the AutoSum button that inserts the SUM function because it will automatically include the cells above.

Objective

Before we get started, let’s be clear about what we are trying to do. If you want to insert the SUM function, and you want it to automatically include the adjacent values above, then you can use the built-in AutoSum button or the Alt+= keyboard shortcut. This is illustrated below.

Excel SUM function by Jeff Lenning

We will build a macro that inserts a SUBTOTAL function in a similar manner, as shown below.

Excel SUBTOTAL function by Jeff Lenning

We will also set up a button and corresponding keyboard shortcut to make it easy to run anytime. Can we do that? We can, and we will. Now, let’s get started.

Overview

The steps to create such a macro are as follows.

  • Record starter macro
  • Edit macro
  • Set up button in the QAT

Let’s begin with the first step.

Note: if you want to get the Excel macro without all of the explanation, just shoot to the end of the post and download the Excel file which contains the working macro.

Record starter macro

We want to record a starter macro that will set up just the basics. It won’t accomplish our goal, but, it will set up a working macro that we can easily edit.

First, create a new blank workbook.

Next, enter some numbers in a few cells. Then, select the cell under the numbers. Your worksheet should look something like this.

20151112c

Next, we want to tell Excel to record us entering a SUM function. To start the macro recorder, select the following Ribbon icon.

  • View > Macros > Record Macro

Note: The Macros icon is split and the top half opens the Macro dialog box and the bottom half reveals a drop-down menu where you can select Record Macro.

After you select Record Macro, Excel will display the Record Macro dialog box. For the Macro name, you can use any name you’d like but avoid spaces and funky characters. I used the name InsertSUBTOTAL. Leave the Shortcut key field empty. Then, you want to store the macro in the Personal Macro Workbook. At this point, your Record Macro dialog box should look like this.

Excel Record Macro dialog by Jeff Lenning

Click the dialog’s OK button and right now, Excel is watching everything you do and it is recording your mouse and keyboard commands. Thus, right now, before you click anything else, you want to tell Excel to insert the SUM function, and you can do so by clicking the Formulas > AutoSum button or by typing the Alt+= keyboard shortcut. Then press Enter on your keyboard to store the formula in the active cell.

Now, STOP the recorder. To stop the macro recorder, you can select the following Ribbon command.

  • View > Macros > Stop Recording

Note: Stopping the recorder is a very important step. If you forget to stop the recorder then your subsequent Excel keyboard and mouse commands will be stored as part of the macro. 

At this point, you should have inserted the SUM function, pressed Enter, stopped the recorder, and your worksheet should look something like this.

Excel total by Jeff Lenning

Now, we have the basic starter macro recorded, and it is time to edit it.

Edit macro

In the step above, we stored the macro in the Personal Macro Workbook. This is a hidden workbook (named PERSONAL.XLSB) that is open whenever Excel is open. That means we can run the macro anytime we are running Excel. To edit a macro stored in the Personal Macro Workbook, we need to unhide it. To do so, click the following Ribbon command.

  • View > Unhide

In the resulting Unhide dialog box, confirm that PERSONAL.XLSB is selected as shown below, and then click OK.

Excel Unhide dialog by Jeff Lenning

We can now see the PERSONAL.XLSB workbook in Excel and so we are ready to edit the macro.

To edit the macro, we select the following Ribbon icon.

  • View > Macros

In the resulting Macros dialog box, we select our new InsertSUBTOTAL macro as shown below.

Excel macro by Jeff Lenning

We now click the Edit button to open the Visual Basic Editor, which should look something like the screenshot below.

Excel Visual Basic Editor by Jeff Lenning

If you have not used the Visual Basic Editor before, it operates very much like a word processor, and you can edit the text, delete text, type new lines, copy/paste, and so on. Our modifications should be fairly easy.

Rather than show screenshots of the macro as we proceed with our edits, I will simply display the macro text as shown below.

Sub InsertSUBTOTAL()
 '
 ' InsertSUBTOTAL Macro
 '
 ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
 Range("A5").Select
End Sub

First up, let’s delete the extraneous lines. The updated macro is shown below.

Sub InsertSUBTOTAL()
 ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
End Sub

Note: if the macro you recorded is different than the above, you can replace your recorded macro by copying the three lines above and pasting into your visual basic editor.

The remaining macro line is the one that inserts the SUM function. It has a strange notation. Instead of using a traditional A1 style range reference, such as A1:A3, it uses an RC (Row Column) notation. The notation R[-3]C tells Excel to go up three cells and stay in the same column as the active cell. The notation R[-1]C tells Excel to go up one cell and stay in the same column as the active cell. Since our formula was inserted into A4, we can see that A1:A3 is the same range as R[-3]C:R[-1]C.

So, we have two edits to make to this macro line: we need to use SUBTOTAL instead of SUM and we need to update the RC range so that it dynamically includes the values in the cells above the active cell.

Let’s start with the easy part. We need to tell Excel to insert the SUBTOTAL function instead of the SUM function. The SUBTOTAL function has an extra argument that the SUM function doesn’t have, and this extra argument tells Excel what type of math to apply to the range. We want our SUBTOTAL function to add the values in the range, so, we need to use code 9, because 9 is the code for sum. Thus, we want to replace “SUM(” with “SUBTOTAL(9,” as shown below.

Sub InsertSUBTOTAL()
 ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)"
End Sub

Now, we need to replace the -3 with a variable that will adapt to the worksheet so that the SUBTOTAL function references the populated range immediately above the active cell.

First, we will make a note of the current row. This is done by inserting the macro line “currow = ActiveCell.Row” as shown below.

Sub InsertSUBTOTAL()
 currow = ActiveCell.Row
 ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)"
End Sub

Next, we need to make a note of the first row with data. This is done by inserting the line “firstrow = ActiveCell.End(xlUp).End(xlUp).Row” as shown below.

Sub InsertSUBTOTAL()
 currow = ActiveCell.Row
 firstrow = ActiveCell.End(xlUp).End(xlUp).Row
 ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)"
End Sub

Next, we need to determine the number of rows between the first row and the current row, and this is done with simple subtraction by adding the line “diff = currow – firstrow” as shown below.

Sub InsertSUBTOTAL()
 currow = ActiveCell.Row
 firstrow = ActiveCell.End(xlUp).End(xlUp).Row
 diff = currow - firstrow
 ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)"
End Sub

Next, we need to update the formula so that instead of using -3 it uses our computed difference, which is shown below.

Sub InsertSUBTOTAL()
 currow = ActiveCell.Row
 firstrow = ActiveCell.End(xlUp).End(xlUp).Row
 diff = currow - firstrow
 ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & diff & "]C:R[-1]C)"
End Sub

Now, we want to save our macro so we save the Personal Macro Workbook. We can hide it again if desired by selecting the following icon.

  • View > Hide

With our macro set up, we just need to make it easy to run. For this, we’ll set up a button in the Quick Access Toolbar (QAT).

Set up button in the QAT

To add a button the Quick Access Toolbar (QAT), use the little drop-down to the right of the QAT and select More Commands. In the resulting Excel Options dialog, you want to Choose commands from Macros. Then, you select the InsertSUBTOTAL macro and Add it to the QAT, as shown below.

Excel Options by Jeff Lenning

Now, we should be good. To test it out, we select a cell and press the new QAT button, as shown below.

Excel SUBTOTAL macro by Jeff Lenning

The macro inserts the SUBTOTAL function…we did it!

The QAT automatically adds Alt shortcut keys as well. Press the Alt key on your keyboard and you’ll see little numbers appear for the QAT command buttons. You can activate the SUBTOTAL macro either by clicking the icon with your mouse or by using the corresponding Alt keyboard shortcut.

With our macro in place, we can now quickly insert the SUBTOTAL function into our workbooks. If you have any other fun macros or ways to insert the SUBTOTAL function, please share by posting a comment below…thanks!

Note: When you close Excel you may receive a dialog box asking if you want to save changes to the Personal Macro Workbook. Click Yes, otherwise, your work may not be saved.

Additional Resources

This article was written by Jeff Lenning

30 comments:

  1. Tom Titus
    Reply

    I tried creating this macro and I keep getting an error message that comes down to the firstrow is empty.

    1. jefflenning Post author
      Reply

      Tom,
      I’m happy to help troubleshoot that with you. Please paste in a copy of your macro and I can see what may be causing the issue.
      Thanks
      Jeff

  2. Bethany
    Reply

    I am having the same trouble as Tom
    Sub InsertSUBTOTAL()
    currow = ActiveCell.Row
    firstrow = ActiveCell.End(x1Up).End(x1Up).Row
    diff = currow – firstrow
    ActiveCell.FormulaR1C1 = “=SUBTOTAL(9,R[-” & diff & “]C:R[-1]C)”
    End Sub
    Can you help me out?

    1. jefflenning Post author
      Reply

      Bethany,

      Happy to help, and thanks for pasting in the code. The End functions on the firstrow line should actually be xlUp (letter l) rather than x1Up (number 1). I’m hoping that if you update this your macro should work. Please let me know?

      (Also, the sample Excel file has the macro in there in case you want to copy/paste.)

      Thanks
      Jeff

  3. Shauna Otttman
    Reply

    Thank you SO much! This will save me a ton of time! 🙂

    1. jefflenning Post author
      Reply

      Welcome 🙂

  4. Rob
    Reply

    Is there a way to change the active cell after the macro runs to the current SUBTOTAL cell? The macro makes the active cell upon complete the one from the original macro recording.

    1. jefflenning Post author
      Reply

      Rob,
      Sure, that is no problem. My best guess is that your macro has an extra line that changes the active cell after inserting the formula, which probably looks something like Range(“A1”).Select. This is easy to fix…you just want to edit the macro using the Visual Basic Editor, and then remove any lines after the line that sets the formula (ActiveCell.FormulaR1C1 = “…”).

      Hope this helps!

      Thanks
      Jeff

  5. John Atamanczyk
    Reply

    Jeff,

    Great post, as always :). I am using Excel 2013 and have heard that the ribbon is easier to customize in this version of Excel. I am curious if you could show this same tip or something else with the button being added to the ribbon instead of the QAT? Or have you done another post already that would show something like that?

    1. jefflenning Post author
      Reply

      Howdy John!
      Oh yes, Ribbon Customization is indeed easy in Excel 2013. Head into the Excel Options dialog box, and instead of clicking Quick Access Toolbar (left side of dialog), select Customize Ribbon instead. You’ll be able to create a New Tab, or a New Group within an existing tab, and then Add icons for your macro or for the built-in commands as desired.
      And thanks for expressing an interest in learning more about that, I will definitely consider it for a future post topic!
      Thanks,
      Jeff

  6. Kathy Penner
    Reply

    Jeff,
    This info is GREAT! I thought I’d try to add the same thing only to count the entries instead of subtotaling them, but I got an error “Can’t execute code in break mode”
    What did I do wrong?
    Sub InsertCOUNT()
    currow = ActiveCell.Row
    firstrow = ActiveCell.End(xlUp).End(xlUp).Row
    diff = currow – firstrow
    ActiveCell.FormulaR1C1 = “=COUNT(R[-” & diff & “]C:R[-1]C)”
    End Sub

    1. jefflenning Post author
      Reply

      Hi Kathy!
      To change the macro to do a count instead of a sum, you can continue to use the SUBTOTAL function as before, and just change the 9 to a 2. So, instead of:
      ActiveCell.FormulaR1C1 = “=SUBTOTAL(9,R[-” & diff & “]C:R[-1]C)”
      You would use this:
      ActiveCell.FormulaR1C1 = “=SUBTOTAL(2,R[-” & diff & “]C:R[-1]C)”

      To make this change, open up the Visual Basic Editor and just change the 9 to a 2 and you should be set!

      Thanks
      Jeff

  7. Gary
    Reply

    Just to be sure I understand this one, once the macro is created, then I can place it in various places in the spreadsheet and run the macro to “magically” subtotal each range regardless if some are short (3 rows) and others are long (50 rows)?

    1. jefflenning Post author
      Reply

      Gary,
      Ideally, you would store the macro code in the personal macro workbook, and that way the macro code is stored in one single location. Then, you can execute the macro anytime Excel is open on any workbook, and it will insert the SUBTOTAL function and include the adjacent range above the formula cell to include short ranges and long ranges. Once the macro runs, the SUBTOTAL function is stored in the workbook and there is no need to also store the macro code in the workbook as well. Essentially, the macro simply writes the formula for you.
      Hope it helps!
      Thanks
      Jeff

  8. Valeri Stevens
    Reply

    Jeff, this macro for =SUBTOTAL is awesome when added to the QAT! I used your instructions to create the macro — it was my first macro. I’m looking forward to learning more about macros.

    After reading Kathy Penner’s 12/2/15 comment and your reply, I used Kathy’s idea and created my second macro, a count macro. It’s identical to the first macro except — per your instructions — I changed the argument to “2” (to count cells with a value or formula) and added it to the QAT, too.

    I’ve arranged the icons on the QAT so that =SUBTOTAL (to sum) is invoked by ALT+9. Since the argument for summing with =SUBTOTAL is 9 and the argument for counting with =SUBTOTAL is 2, I considered arranging the icons on the QAT, so that the “count” macro would be invoked by keying in ALT+2. In the end, I decided to make the count macro ALT+8 on the QAT. The icons are the same for both, which may be confusing, but the Alt+9 position for =SUBTOTAL to sum should help me remember which is which.

    I’m so happy you shared this great macro.

    1. jefflenning Post author
      Reply

      Awesome 🙂

  9. Konno
    Reply

    i do thing follow your instruction and it work as charm. After i have the result of subttotal macro, i want to make a macro to paste result to other sheet to make report can you please show me how to do that.
    I just do record macro -> Hotkey macro of subtotal-> copy result -> paste to report sheet -> chose kind of report (value only) -> end macro. But it don’t work.
    Hope here your solution soon. Tks you.

    1. Kurt LeBlanc
      Reply

      Hey Konno,

      I can’t seem to replicate the issue you’re having…It works perfectly as per your instructions…Maybe download a new file and start over.

      Please let me know how it works out for you
      Kurt LeBlanc

  10. Roy Rouessart
    Reply

    Thanks Great Macro. Will it work where there either hidden rows or where there is a blank cell in the range.
    Thanks again

    1. Kurt LeBlanc
      Reply

      Hey Roy

      The function goes to the last data cell up so it’ll stop at a blank cell:)
      As for the hidden row, whether you want hidden rows included in the calculation affect the code you’ll need in your macro. 9 will include them and 109 will exclude them. Hope that helps clarify things!

      Let me know if it doesn’t and I’m happy to help:)
      Kurt LeBlanc

      1. Johnny V
        Reply

        Hi, this has been an extremely helpful exercise. However, I am having a slight issue. Whereas instead of the subtotal formula it automatically creates, capturing a column of values immediately above it, and stopping at a blank cell. The subtotal seems to skip any number of blank cells, and extends to the last value in a column of values that reside anywhere above it. Even if i create 100 blank lines above it, it seems to extend past the blank cells and to the last value of the column of values anywhere above it. Is there an additional command i can include to prevent this, keeping its focus only on the values directly above it?

        1. Kurt LeBlanc
          Reply

          Hi Johnny

          Can you furnish me with a copy of your code for the macro? I can help you better after that.

          Thank you,
          Kurt LeBlanc

  11. Ben
    Reply

    Gary:
    I took your Excel 3 Seminar today, it was AWESOME
    I set the macro up and it runs fine from Run Macro.
    I added to the QAT according to the instructions but it doesn’t seem to want to run?

    1. Kurt LeBlanc
      Reply

      Hey Ben,

      hmm…I can’t seem to replicate the problem on my end…maybe download another sample file and try again. I’m sorry I can’t help you anymore than this.

      Let me know if you get it
      Kurt LeBlanc

  12. Robin Cooke
    Reply

    Jeff,
    I took your complementary class today on excel tips through CPA Academy. I asked you if there was a quick access tool for this function and you delivered! My coworker had taken your webinar last year and told me about the excellent subtotal function, but for economy of key strokes it was still faster to click autosum(sigma) and then clck and pick the totals I needed in the grand total, rather than type all that function command language every time. Now with this macro I am giving up the Sum for good. Thanks!–Robin

    1. Jeff Lenning Post author
      Reply

      SUM No More 🙂

  13. Alfredo Funes
    Reply

    Jeff, what is I have a title and I want it to be excluded from the “firstrow” part?

    1. Jeff Lenning Post author
      Reply

      You can update the firstrow variable to add 1 row, so, something like this:
      firstrow = ActiveCell.End(xlUp).End(xlUp).Row + 1

      Hope it helps!
      Thanks
      Jeff

  14. Luevenia
    Reply

    Hi Jeff,
    Just took the free class on March 2 about the subtotal macro and come to this site to copy and past the macro into the PERSONAL.XLSB workbook. The subtotals are working wonderfully, however when I am trying to do a grand total it is only pulling the numbers for the last subtotal. Has anyone else experienced this problem and what is the fix. Thanks.

    1. Jeff Lenning Post author
      Reply

      Hi! The macro is designed to only include the cells immediately above the formula that are populated, similar to the AutoSum button. So, if the formula that the macro creates improperly excludes values above, you can manually update the cell reference as needed.
      Thanks
      Jeff

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.