SUBTOTAL more than 254 rows

问题:

I am auto generating a spreadsheet via VBA with budgetary data. The rows are variable. I use the following code for an R1C1 formula. Variable r is a row counter.

        '-----------------
        'Subtotal formula
        '-----------------
        'Build formula string
        formulaString = "=SUBTOTAL(109"
        For i = 13 To r - 13
            formulaString = formulaString & ",R[" & i & "]C"
            i = i + 11
        Next i
        formulaString = formulaString & ")"

        'Subtotal formulas
        .Range("I5:U6").FormulaR1C1 = formulaString

This works great until the references exceed 254. Is there an easy way build this R1C1 formula such that: For each cell in range I5:U6, SUBTOTAL 109 every 13th row beneath to max row? If there is an easy answer could you please explain it step by step? Thanks.

回答1:

If you can't sort your data to avoid that, or run the sum in a user defined function, you can add a second loop to count until 254 and when you hit 254, close the formula string and reopen one, as in this updated code:

    'Build formula string
    formulaString = "=SUBTOTAL(109"
    lCount=1
    For i = 13 To r - 13
        formulaString = formulaString & ",R[" & i & "]C"
        i = i + 11
        lcount=lcount+1
        if lcount>250 then 
           formulaString = formulaString & ")+ SUBTOTAL(109"
           lcount=1
        end if                        
    Next i
    formulaString = formulaString & ")"

    'Subtotal formulas
    .Range("I5:U6").FormulaR1C1 = formulaString

回答2:

Another option to total the 13th rows would be to use a SUMPRODUCT with MOD. This will not have the features of the subtotal, such as not totalling hidden rows, but it will be a simpler formula, e.g.:

=SUMPRODUCT(A1:A100*(MOD(ROW($A$1:$A$100),13)=0))