Excel 2003 VBA - Subtotals & Dynamic Arrays 1

[greengo204]

I am trying to create a Macro that sorts data and then adds subtotals. This macro is to be used on different spreadsheets with different numbers of columns. Although the macro is to be used on different spreadsheets the first 4 columns are to always be excluded from the subtotaling so at current my code looks like:

Sample Code:

    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
        , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
        8), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2

Sample data:

A/C Code A/C Name A/C Manager N/C Grand Total Fixed Trans International
xxxxxx xxxxx East 2201301 1000 500 500 0
gggggg ggggg North 2201301 3000 2000 1000 0
kkkkkk kkkkkk South 2201301 4000 1000 1000 2000
nnnnnn nnnnn West 2201301 5000 3000 2000 0


When ever I try run this macro where “TotalList:=Array” does not exactly match the number of columns in a spreadsheet I encounter the following error “subtotal method of range class failed”

My problem is that I’m trying to create a dynamic array for the “TotalList:=” so I can add subtotals to all columns to the right of column 4 (N/C) in the above case 5 to 8. In some cases the number of columns can be up to 50.


Cheers

 

[SkipVought]

Hi,

My problem is that I'm trying to create a dynamic array for the "TotalList:=" so I can add subtotals to all columns to the right of column 4 (N/C) in the above case 5 to 8. In some cases the number of columns can be up to 50.

Your SOURCE data is not well structured. Source Data should not have a varying number of columns; columns where the Column Heading is really a data element. What you have is a REPORT structure and not a TABLE structure. If your data were in a normalized table structure, then Excel's reporting tools could REPORT a varying number of columns, using the PivotTable wizard, along with the subtotaling that you desire. Such a solution could be achieved in SECONDS, with out VBA code. BUT you data must be properly structured in order to take advantage of this powerful reporting tool.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[greengo204]

my source data comes from a pivot table/s. The colums change in lengh because from month to month different catagories may be applicable.

example (use with above example) if there was no "International" total due to no international products being used then i would have to change my macro to reflect the decrease in columns.

the origanal pivot table format is:

Page:Business Area
Row: A/C Code, A/C Name, A/C Manager, N/C
Column: Revenue Type
DATA: Sum of Amount

Currently i produce report based on business area which have varied revenue types between them. My process is to copy the pivot table result into a blank spreadsheet (As Values) and perform formatting, sorting and subtotalling on the results.

Hope i am clearer on my aim.

cheers

 

[SkipVought]

Why are you not using the subtotaling in the PivotTable?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[greengo204]

I do use them on some occasions but not for the above example because the people that will be using these reports have very basic Excel skills and using the subtotals (Data>Subtotal) method has proved much more 'easier' for them to use, as they simple have to expand there 'A/C Manager' field to see their breakdown and not worry about combining different fields and selections together in Pivots.

Training of users in Pivot tables is not an option.

They must be able to expand and collapsed as in some cases they may have +-1000 rows of data for each 'A/C Manager'

sorry to be a pain...

cheers

 

[greengo204]

found a similar problem to mine without a solution unfortunetely:

http://www.ozgrid.com/forum/showthread.php?t=20840

 

[SkipVought]

What a mess!

Build an array.

Sub Macro4()
    Dim arry() As Integer, r As Range, i As Integer, iCol As Integer
'
    i = 1
    'aggregate from the 4th column to the end
    iCol = 4
    For Each r In Range(Cells(1, iCol), Cells(1, iCol).End(xlToRight))
       ReDim Preserve arry(i)
       arry(i) = r.Column - (iCol - 1)
       i = i + 1
    Next
    
    Cells(1, 4).CurrentRegion.Subtotal _
        GroupBy:=1, _
        Function:=xlSum, _
        TotalList:=arry, _
        Replace:=True, _
        PageBreaks:=False, _
        SummaryBelowData:=True
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[greengo204]

SkipVought:

I still ran into the same error using the above code, the 'arry' varible was returning 0, did manage to find a solution though which is working:

Dim TotColumns()
Dim I As Integer
FinalCol = Cells(5, Columns.Count).End(xlToLeft).Column
ReDim Preserve TotColumns(1 To FinalCol - 4)
For I = 5 To FinalCol
TotColumns(I - 4) = I
Next I

Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=TotColumns, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

Cheers for your help.

 

[SkipVought]

I would also advise to avoid using the Selection property. Rather explicitly reference the object.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}