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.


    Code
    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?


    Code
    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 -

    Code
    ActiveCell.SpecialCells(xlLastCell).Select


    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 & ")
    =array("x")


    Any sugestions how this could be done?

  • Re: Vba Increase Columns In Subtotal


    So, you already tried this?

    Code
    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?


    Mark

  • 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?


    regards
    Peter

  • 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.


    Thoughts:


    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



    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


    Peter

Participate now!

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