Vba Increase Columns In Subtotal

  • I want to automate the subtotal function by using VBA macro but do not know how to cope with an expanding range of columns in the macro. The following is the code, so far, to run the subtotal function, where the array states the numbers of the columns to be sub-totalled. The columns will increase linearly from 8.

    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8) _
            , Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    can anyone give me a pointer please?

  • Re: Vba Increase Columns In Subtotal

    I don't generally mess around with subtotals too much. Instead of using the various columns listed, can you use Columns("E:H") in the macro?

    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(Columns("E:H")) _
            , Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    If that works (and I don't know if it will for certain), all you'd have to do is then determine where is the first column, which you should know since you bring in the data, and where is the last column. Then, instead of - Columns("E:H") - you'd use use variables in their place, such as Columns(x & ":" & y)

    So, providing you know the first column (which should be A), you could then use something like this to determine the last column:

    Another idea might be to use the -


    and go backwards from there.

    You could also use a loop and generate the (5, 6, 7, 8) string, adding a number, comma, space for each entry found in the array if necessary.

  • Re: Vba Increase Columns In Subtotal

    Hi MrKFrrl

    Thanks for help.

    Have tried columns in the array and it comes up with Run-time error '13' type mismatch.

    Your second suggestion of building the string outside of the code could work but I cannot find any way of inserting the new string into the array.

    I have tried defining a variable say x as (5,6,7,8) and then tried to insert it into the code by a variety of methods none of which work and all of which give run-time error 1004.

    methods used =array(x)
    =array(" & x & ")

    Any sugestions how this could be done?

  • Re: Vba Increase Columns In Subtotal

    So, you already tried this?

    Dim x as String ' in module header (or maybe it needs to be Dim x as Variant )
    x = "5, 6, 7, 8" ' at top of macro
    =array(x) ' within the context of your array macro

    And that did NOT work?


  • Re: Vba Increase Columns In Subtotal

    Mark, thanks for the time

    I have now tried your suggestion using both dim as string and as variant and I'm afraid they do not work. Any other sugestions?


  • Re: Vba Increase Columns In Subtotal

    I'm at a loss why it won't accept a variable in this field. I see you found another thread that went unanswered a few years ago too.

    And I really don't mess with Subtotals enough to see why it doesn't work.


    1.) Could it be because the data in question are not entered in an array format yet at the time the Subtotal is to be calculated?

    2.) If #1 is no, will the number of columns vary by much--that is, could you write several macros, each with a different number of column rows in the Subtotal macro? You'd then calculate the number of columns, and call whichever Subtotal macro would be appropriate.

    3.) If #2 is too difficult, is there another method that the data could be calculated without using Subtotals and Arrays (e.g., Sumproduct, etc.)?

    Don't know what else to tell you.

    You could always ask Dave Hawley, if he's not too busy. I think he knows everything there is to know about Excel.

    If that doesn't work, ask John Walkenbach, who also knows everything.

    Let me know what you come up with, as I'm curious.

  • [Solved] Re: Vba Increase Columns In Subtotal

    Hi Mark

    I have solved my problem as follows:

    in working area of workbook create a range of the column numbers

    cell C2 D2 E2 F2
    5 6 7 8

    then in vba include the following code

    Dim Data()
              Data = Range("C2:F2")
              Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(Data) _ 
    , Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    It works a treat

    Thanks for all your help


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!