none
Subtotal dynamic range RRS feed

  • Question

  • Hello,

    The following subtotal code works great when there are 26 columns, ie Array(4-30).

    However, this sheet does not always contain that many columns.  It does always begin at Array(4,  but ends anywhere from 6 to 32.

    How can this be made dynamic to accommodate different numbers of columns ..??

     Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
            8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30), Replace _
            :=True, PageBreaks:=False, SummaryBelowData:=True

    Thanks in advance.

    Saturday, May 28, 2016 2:09 PM

Answers

  • Try...

        Dim aTotalList() As Integer
        Dim iCol As Integer
        With Selection.CurrentRegion
            ReDim aTotalList(1 To .Rows(1).Cells.Count - 3)
            For iCol = 4 To .Rows(1).Cells.Count
                aTotalList(iCol - 3) = iCol
            Next iCol
            .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=aTotalList, _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        End With

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Mikf Monday, May 30, 2016 3:54 PM
    Saturday, May 28, 2016 11:24 PM

All replies

  • Try...

        Dim aTotalList() As Integer
        Dim iCol As Integer
        With Selection.CurrentRegion
            ReDim aTotalList(1 To .Rows(1).Cells.Count - 3)
            For iCol = 4 To .Rows(1).Cells.Count
                aTotalList(iCol - 3) = iCol
            Next iCol
            .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=aTotalList, _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        End With

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Mikf Monday, May 30, 2016 3:54 PM
    Saturday, May 28, 2016 11:24 PM
  • Hi Mikf,

    did you check the suggestion given by the Domenic Tamburino.

    if you did not checked it then I would recommend you to check it first and let us know if it worked for you or not so that we can try to provide further suggestions.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 30, 2016 1:08 AM
    Moderator
  • Great Dominic, thanks ..!

    Monday, May 30, 2016 3:54 PM
  • That's great.  Thanks for the feedback.

    Cheers!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Monday, May 30, 2016 5:31 PM