1

I received the errorbox: "Subtotal method of Range class failed" with the below code. How can I fix this and how can I rewrite the code so that it can accommodate a changing number of columns to subtotal?

    Range("A1").Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(14, 15, 16 _
    , 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, _
    43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63), Replace:= _
    True, PageBreaks:=False, SummaryBelowData:=True
lindqmx1
  • 79
  • 1
  • 2
  • 8
  • Does this help: http://stackoverflow.com/questions/23276881/run-time-error-1004-subtotal-method-of-range-class-failed – Vityata Jun 24 '16 at 13:30
  • 1
    *how can I rewrite the code so that it can accommodate a changing number of columns* - you'll need to loop through each column and load the column number into an array variable, and pass that to the `TotalList` argument. – Scott Holtzman Jun 24 '16 at 13:47

1 Answers1

0

If that's an array, you may build it like the following 

Dim ArrayNumbers() As String
Dim CounterArray As Long
For CounterArray = 1 To 49
ReDim Preserve ArrayNumbers(CounterArray)
'I'm not quite sure if parsing empty elements in the 'Total List' arg would give error, so instead we are going to start in the element 1 of the array doing the value 14 for it and so on.
ArrayNumbers(CounterArray) = IIf(CounterArray <> 1, "," & CounterArray+14, CounterArray+14) 'this is to avoid the comma in the first value just for all the other ones
Next CounterArray
Range("A1").Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=ArrayNumbers, Replace:= _
    True, PageBreaks:=False, SummaryBelowData:=True
Sgdva
  • 2,800
  • 3
  • 17
  • 28
  • "Run-time error 1004 Subtotal method of Range class failed" at the `Selection.Subtotal` line. Also could I change `49` to `i` if I wanted to input the column number myself? – lindqmx1 Jun 28 '16 at 13:17
  • Yeah you can, what are you trying to achieve with that code? – Sgdva Jun 28 '16 at 13:42
  • Whenever column C's data changes, I need to add row subtotals for a variable amount of columns. I know how to add a variable in the counterArray, but as is, the code gets the above error. – lindqmx1 Jun 29 '16 at 16:22