Using variables with ParamArray 1

[Storyteller]

Hello All,
I am using the "subtotal" menu command to group and subtotal a list of information. The code created is the following:

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6,7,8), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

The issue is that the number of colums in the Array() changes. The first value is always 6 however the ending column number may change. As the range of data will change on a regular basis.

I use the following to determine the last column number:

FC = Cells(1, Columns.Count).End(xlToLeft).Column

So if FC equals 9, I want the Array() to have the value of Array(6,7,8,9)

What I am stuck on is how to change the Array() values. I have found the ParamArray(), but I have not been able to figure it out or perhaps there is another solution?

Any suggestions would be greatly appreciated.

Regards,
Michael

 

[PHV]

A starting point:

Dim arrCol()
FC = Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arrCol(FC - 6)
For i = 6 To FC
  arrCol(i) = i
Next
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=arrCol, _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Storyteller]

Hello PHV,
Thank you for your reply. This is the (updated) code using your example:

Sub CreateSubtotal()
    Dim arrCol()
    Dim FC As Long
    Dim i As Integer
    FC = Cells(1, Columns.Count).End(xlToLeft).Column '<--This value is 8
    ReDim arrCol(FC - 6)
    For i = 6 To FC
      arrCol(i) = i '<--breaks here with a subscript out of range error
    Next
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=arrCol, _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

As you can see I am getting a subscript out of range error. I have to admit working with Arrays is not my strongest suit.
I really appreciate the help you are offering.

Regards,
Michael

 

[PHV]

Sorry for the typo:
arrCol(i - 6) = i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Storyteller]

Hello PHV,
It works!!! Thank you. Now, all I have to do is figure how/why it works

Here is the complete working code in case someone else is looking for a similar solution.

Sub CreateSubtotal()
    Dim arrCol()
    Dim FC As Long
    Dim i As Integer
    FC = Cells(1, Columns.Count).End(xlToLeft).Column
    ReDim arrCol(FC - 6)
    For i = 6 To FC
      arrCol(i - 6) = i
    Next
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=arrCol, _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

I'm also sending you a star for your help.