Creating a Variable Array and using it in a Sort Function

[JohnHenry]

I'm trying to create a variable array and then use it in a sorting function. I am trying many different things to fix it, each time getting different errors. In a non-variable example, the bolded portion below reads TotalList:=Array(8,9,10,11,12,13,14). For this month, VehCol and LastColData are Integers 8 and 14 respectively. I need general array creation and implementation help. Thank you in advance.

''''''''''CODE START

Dim SubTotalColumns() As Integer
Dim n As Integer


For n = VehCol To LastColData
ReDim Preserve SubTotalColumns
SubTotalColumns = n
Next n

Selection.Subtotal GroupBy:=7, Function:=xlSum, TotalList:=SubTotalColumns, Replace:=True, PageBreaks:=False, SummaryBelowData:=True

''''''''''CODE END

 

[Peter_SSs]

Try this for setting the SubTotalColumns array

Dim SubTotalColumns  As Variant

SubTotalColumns = Application.Transpose(Evaluate("row(" & VehCol & ":" & LastColData & ")"))

 

[JohnHenry]

Peter,

Thank you for the reply. The code works (with your change) through my sorting line calling the array. I get a 1004 error: "Subtotal method of Range class failed." I am not very familiar with arrays and I am not sure how to even check the values of my arrays in an easy manner for my own error-checking. Am I even calling it correctly?

Thanks again for your help and any future help would be appreciated.

John

 

[Peter_SSs]

John

1. Your topic title and text in your posts keeps referring to Sorting, though your code appears to be Subtotalling, not sorting.
That is confusing and clarification would help if you need to make further posts in this thread.
I have assumed Subtotalling since that is what is in your code.

2. The error you reported could be because the data to be subtotalled is not selected when the code gets to the subtotal line. You would need to either ..
a) Select the data before activating the code, or
b) Have the code select the data before it gets to the subtotal line, or
c) Best choice: Not select the data at all but have the code refer to the appropriate data along the lines of the following (which only guesses at the location & extent of your data to be subtotalled)

With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, LastColData)
  .Subtotal GroupBy:=7, Function:=xlSum, TotalList:=SubTotalColumns, Replace:=True, _
    PageBreaks:=False, SummaryBelowData:=True
End With

3. To check what is in your arrays, in the vba window ensure that the 'Locals' window is visible (Menu: View > Locals Window). Then place your cursor somewhere in the code and press F8. Continue pressing F8 to step through a line at a time. After the Subtotals array is processed (Application.Transpose line of code) you can open out that array in the locals window and inspect the individual elements.
By stepping through the code like this you can also monitor what is happening on the sheet, which often helps with de-bugging or improving code.

4. For the record, you were pretty close with your original code. To set the array in a loop as you were attempting, you could do it like this ( I always prefer Long to Integer)

Dim SubTotalColumns() As Variant
Dim n As Long, VehCol as Long, LastColData as Long
.
.
'Find or set VehCol and LastColData
.
.
ReDim SubTotalColumns(1 To LastColData - VehCol + 1)
For n = VehCol To LastColData
  SubTotalColumns(n - VehCol + 1) = n
Next n

 

[Norie]

John

Try replacing Selection with the actual data range

This worked for me with the data in A1:N20.

    SubTotalColumns = Evaluate("TRANSPOSE(ROW(A9:A14))")
    
    
    Range("A1:N20").Subtotal GroupBy:=7, Function:=xlSum, TotalList:=SubTotalColumns, _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True

 

[JohnHenry]

Peter,

You are very right about my topic choice and word choice. I apologize for the confusion. I have been working on the same code all weekend and only recently jumped from a sorting problem into a subtotal problem. I will look into your corrections but I think I have enough now where I can sort it out myself :wink:. Especially thank you for the locals window as I can hopefully do a little more troubleshooting on my own. If I have any further troubles, I will reply to this thread.

Thanks again,
John

 

[Peter_SSs]

.. I think I have enough now where I can sort it out myself :wink:. .... If I have any further troubles, I will reply to this thread.

Sounds like a good way to go.