Code for Subtotals

[robs3131]

Hi,

I am trying to use the Excel Subtotals function for a scenario where the
number of columns needing to be subtotaled varies -- there may be 4 columns
in one run of the code, but there may be 20 columns in the next run. Below
is the recorded code I have tinkered with -- but I am stuck on how to code it
in a loop so that the number of columns can be set differently each time the
code is run. Any suggestions are appreciated!

'The headers are in row 2 and the actual data to be subtotaled begins in
row 3
With Sheets("Commission by Entity breakdown")
.Range("A2").subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 6,
7, 9, _
11, 13, 15, 17, 19, 20, 22, 24, 26, 28, 29, 31, 33, 34, 35,
37), Replace:=True, PageBreaks:= _
False, SummaryBelowData:=False
End With

 

[Bob Phillips]

How about this

'The headers are in row 2 and the actual data to be subtotaled begins in
Row 3
Dim rng As Range
Dim cell As Range
Dim colGroup As Range
Dim aryCols As Variant
Dim i As Long

With Sheets("Commission by Entity breakdown")

On Error Resume Next
Set rng = Application.InputBox("Select columns to subtotal with the
mouse", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then

ReDim aryCols(1 To 1)
For Each colGroup In rng.Areas

For Each cell In colGroup.Columns

i = i + 1
ReDim Preserve aryCols(1 To i)
aryCols(i) = cell.Column
Next cell
Next colGroup

.Range("A2").Subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=aryCols, _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=False
End If
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[robs3131]

Hi Bob,

When I put your code in, I get a "Runtime error 1004: Subtotal method of
Range class failed" error. I'm guessing the problem is with the "rng"
variable as when I check to see it's address in the immediate window, I get
the following message: "Run-time error '91': Object variable or With block
variable not set".

Also, I want to have the range set systematically (I don't want to require
the user to select the columns to subtotal). I was thinking that I could do
this as follows -- do you think this would work?

set rng = .range("BA2", .range("IV2").end(xltoLeft))

 

[robs3131]

Sorry - actually the "rng" variable is fine -- it is the "colGroup" variable
that I believe is the issue. I've updated my previous post below with an
accurate description of the issue.

 

[Bob Phillips]

Try this

Dim rng As Range
Dim colGroup As Range
Dim aryCols As Variant
Dim i As Long

With Sheets("Commission by Entity breakdown")

On Error Resume Next
Set rng = Range(Range("G2"), Cells(2, Columns.Count).End(xlToLeft))
On Error GoTo 0
If Not rng Is Nothing Then

ReDim aryCols(1 To 1)
For Each colGroup In rng.Columns

i = i + 1
ReDim Preserve aryCols(1 To i)
aryCols(i) = colGroup.Column
Next colGroup

.Range("A2").Subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=aryCols, _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=False
End If
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[robs3131]

Hi Bob - I still get the same error...I also tried qualifying the statement
below by putting a "." in front of each applicable word and still got the
same error.

Set rng = .Range(.Range("G2"), .Cells(2, .Columns.Count).End(xlToLeft))

 

[Bob Phillips]

Robert,

I am stumped then. Can you mail me the workbook?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[robs3131]

Hi Bob - thanks for offering to look at this - I just sent you the file.

 

[robs3131]

Hi Bob - wanted to let you know that I found the problem - it was that there
were some blank cells in my header row (once I populated values into those
cells, the code worked perfectly).

Thanks!

 

[Bob Phillips]

Great. I never got the file that you sent.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)