How to set the SubTotal TotalList using a variant instead of an array

Greetings! I am currently redesigning a workbook that is tracking the daily output of a number of wells. The original tracked 100 wells, but the new version could track anywhere up to 1000 wells. I have been trying to find a method of getting around typing up to 1000 column numbers into the TotalList:=Array (3,4,5,etc.) statement. After a lot of research I have created the following:

Code:

---

Sub WklySubtotal()
    Dim varCols()  As Variant  'array to hold column numbers
    Dim intCount    As Integer  'for..next counter
    Dim intMaxCol  As Integer  'number of columns to subtotaled
   
    Sheets("Sheet1").Select
    Cells(1, 3).Select
    Selection.End(xlToRight).Select
    intMaxCol = ActiveCell.Column
   
    ReDim varCols(intMaxCol - 2)
   
    For intCount = 3 To intMaxCol
        varCols(intCount - 3) = intCount
      ' Debug.Print intCount - 3, varCols(intCount - 3)
    Next intCount

    Selection.Subtotal Groupby:=1, Function:=xlAverage, TotalList:=varCols, _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'    Selection.Subtotal Groupby:=1, Function:=xlAverage, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14), _
'    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
   
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
   
End Sub

---

My sample data is based on 10 wells (the columns listed in the commented out section of code), and it works just fine when I use the version with the array values typed in, but when I try to run it using the varCols variant I get aRun Time error 1004 - Subtotal method of Range class failed.

The Debug statement has shown me that the right column numbers are in the variant array.

I am working in Excel 2013 on Windows 10, but I have been getting the same result in both Excel 2010 and 2016.

Any help is greatly appreciated (I cannot help but feel that I am missing some tiny little thing!)

Avoid 'Select' and 'Activate' in VBA

You can use either method (dependent of the number of columns the currentregion has).

Code:

---

Sub M_snb()
  sn=[transpose(row(3:14)])  '  or
  sn=evaluate("transpose(row(3:" & Sheets("Sheet1").cells(1).currentregion.columns.count & "))")

  sheets("Sheet1").cells(1,3).resize(,Sheets("Sheet1").cells(1).currentregion.columns.count-1).Subtotal Groupby:=1, Function:=xlAverage, TotalList:=sn, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  ActiveSheet.Outline.ShowLevels RowLevels:=2

  Columns(2).Hidden = True
End Sub

---

I tried the second option and received another 1004 error "We couldn't do this for the selected range of cells. Select a single cell within a range of data and try again."
I then tried the first option and got the "Subtotal method of Range class failed" message.
(I did cut and paste your code example, not retype)

This is my first time with VBA Subtotal, but this does compile

Code:

---

Option Explicit

Sub WklySubtotal()
'Most comments herein are for the OP's understanding
    Dim varCols()  As Variant 'array to hold column numbers.
    Dim i          As Long 'for..next counter
    'Dim intMaxCol  As Long 'number of columns to subtotaled
    Dim WellTable  As Range
    Dim BottomRight As Range 'used for clarity, can subsitute assingment for variable
   
    Set WellTable = Range("C1").CurrentRegion
    Set BottomRight = Cells(WellTable.Cells(WellTable.Count))
   
    Set WellTable = Range(Range("B2"), BottomRight) 'Can substitute assignment here
    'intMaxCol = WellTable.Columns.Count
    i = WellTable.Columns.Count - 1
   
    ReDim varCols(i) 'Array is zero based, holds Offsets from Column B
    For i = 1 To i
        varCols(i - 1) = i
    Next i
   
    'Groupby is column B
    WellTable.Subtotal Groupby:=1, Function:=xlAverage, TotalList:=varCols, _
    Replace:=True, SummaryBelowData:=True
   
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Columns("B:B").EntireColumn.Hidden = True
   
End Sub

---

About Programming conventions:
General
i, j, & k are used as counters and indexes. never capitalized

VBA;
Use Long vice Integer, Byte, or Single. Memory is cheap nowadays.
R, r, Rw, rw as Row counters, indexes
C, c, Col, col as Column counters, indexes
Rng, rng as Ranges
Cel, cel as Cells

Personally;
X, Y, & Z as variants for all purposes, but only while testing and troubleshooting

Quote:

---

Columns("B:B").EntireColumn.Hidden = True

---

:devil2:

Code:

---

Columns(2).Hidden = True

---

Thank you snb and SamT. I was able to find an answer that required minimal changes to my code. I changed my counter to start at 2 and added a specific selection line and it works like a charm:

Code:

---

For intCount = 2 To intMaxCol
        varCols(intCount - 2) = intCount
        'Debug.Print intCount - 2, varCols(intCount - 2)
Next intCount

Selection.CurrentRegion.Select

---