Subtotalling

[Carly]

I was wondering if anyone could help me with this urgent query I have.

I am trying to subtotal a spreadsheet within a macro but I am unsure how many columns there will be each time.
When you record the subtotalling it puts in the column numbers (TotalList:=Array(5,6,etc) which is no good as one day there might be 5 and another day there might be 50.

Can this be done by somehow naming a range like in a Pivot Table?

Please help

I realise that I have posted this question before but I am struggling to get any further with my report, Everything I try seems to fail.

Carly

 

[Andrew Poulsom]

See if you can adapt this to your needs:

Sub Test()
    Dim Rng As Range
    Dim x As Integer
    Dim y As Integer
    Dim Arr() As Integer
'   *** Change starting cell in table to suit ***
    Set Rng = Range("A1").CurrentRegion
'   *** Change starting column to suit
    y = 1
    For x = 2 To Rng.Columns.Count
        ReDim Preserve Arr(1 To y)
        Arr(y) = x
        y = y + 1
    Next x
    Rng.Subtotal groupBy:=1, Function:=xlSum, totalList:=Arr
End Sub

 

[Carly]

Thanks

This method has worked fine apart from one thing which I forgot to mention.

The first 4 columns do not need to be subtotalled on. I still want to Group on the first column but the 2nd, 3rd & 4th column don't need to be subtotalled.

Is there anyway within that code I cen stop it doing this?

 

[Andrew Poulsom]

That's contolled by:

For x = 2 To Rng.Columns.Count

So change it to:

For x = 5 To Rng.Columns.Count