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

--
Robert

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" wrote in message
...
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

--
Robert

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))

--
Robert


"Bob Phillips" wrote:

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" wrote in message
...
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

--
Robert

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.
--
Robert


"robs3131" wrote:

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 "colGroup"
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". (FYI - I selected columns "BA:BG" for the "rng" variable and this is showing as such in the immediate window)

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))

--
Robert


"Bob Phillips" wrote:

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" wrote in message
...
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

--
Robert

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" wrote in message
...
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.
--
Robert


"robs3131" wrote:

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
"colGroup"
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". (FYI - I selected columns "BA:BG" for the "rng"
variable and this is showing as such in the immediate window)

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))

--
Robert


"Bob Phillips" wrote:

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" wrote in message
...
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

--
Robert

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))

--
Robert


"Bob Phillips" wrote:

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" wrote in message
...
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.
--
Robert


"robs3131" wrote:

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
"colGroup"
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". (FYI - I selected columns "BA:BG" for the "rng"
variable and this is showing as such in the immediate window)

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))

--
Robert


"Bob Phillips" wrote:

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" wrote in message
...
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

--
Robert