davidmack
02-04-2016, 12:46 PM
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:
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!)
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!)