Subtotalling

Carly

Active Member
Joined
Aug 21, 2002
Messages
370
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
See if you can adapt this to your needs:

Code:
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

Active Member
Joined
Aug 21, 2002
Messages
370
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
That's contolled by:

For x = 2 To Rng.Columns.Count

So change it to:

For x = 5 To Rng.Columns.Count
 

Forum statistics

Threads
1,143,702
Messages
5,720,375
Members
422,281
Latest member
jantoooh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top