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" <(E-Mail Removed)> wrote in message
news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
news:BEE085D0-AF6D-4C7E-A6B7-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> > news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
>> > > 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" <(E-Mail Removed)> wrote in message
> news:BEE085D0-AF6D-4C7E-A6B7-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> > news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
> >> > > 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
> >> >
> >> >
> >> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:F4C6AD7B-1766-4B4E-B06F-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:BEE085D0-AF6D-4C7E-A6B7-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> > news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
>> >> > > 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 - thanks for offering to look at this - I just sent you the file.
--
Robert


"Bob Phillips" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:F4C6AD7B-1766-4B4E-B06F-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:BEE085D0-AF6D-4C7E-A6B7-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> > news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
> >> >> > > 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 - 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!
--
Robert


"robs3131" wrote:

> Hi Bob - thanks for offering to look at this - I just sent you the file.
> --
> Robert
>
>
> "Bob Phillips" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:F4C6AD7B-1766-4B4E-B06F-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > >> news:BEE085D0-AF6D-4C7E-A6B7-(E-Mail Removed)...
> > >> > 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" <(E-Mail Removed)> wrote in message
> > >> >> > news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
> > >> >> > > 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
> > >> >> >
> > >> >> >
> > >> >> >
> > >>
> > >>
> > >>
> >
> >
> >

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)

"robs3131" <(E-Mail Removed)> wrote in message
news:220827FA-DB6D-4B54-AEEC-(E-Mail Removed)...
> 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!
> --
> Robert
>
>
> "robs3131" wrote:
>
>> Hi Bob - thanks for offering to look at this - I just sent you the file.
>> --
>> Robert
>>
>>
>> "Bob Phillips" wrote:
>>
>> > 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" <(E-Mail Removed)> wrote in message
>> > news:F4C6AD7B-1766-4B4E-B06F-(E-Mail Removed)...
>> > > 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" <(E-Mail Removed)> wrote in message
>> > >> news:BEE085D0-AF6D-4C7E-A6B7-(E-Mail Removed)...
>> > >> > 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" <(E-Mail Removed)> wrote in
>> > >> >> > message
>> > >> >> > news:619CA6CB-08A0-44AA-A107-(E-Mail Removed)...
>> > >> >> > > 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
>> > >> >> >
>> > >> >> >
>> > >> >> >
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >