# Creating a Variable Array and using it in a Sort Function

#### JohnHenry

##### New Member
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Peter_SSs

##### MrExcel MVP, Moderator
Try this for setting the SubTotalColumns array
Code:
``````Dim SubTotalColumns  As Variant

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

#### JohnHenry

##### New Member
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

##### MrExcel MVP, Moderator
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)
Code:
``````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)
Code:
``````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``````

Last edited:

#### Norie

##### Well-known Member
John

Try replacing Selection with the actual data range

This worked for me with the data in A1:N20.
Code:
``````    SubTotalColumns = Evaluate("TRANSPOSE(ROW(A9:A14))")

Range("A1:N20").Subtotal GroupBy:=7, Function:=xlSum, TotalList:=SubTotalColumns, _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True``````

#### JohnHenry

##### New Member
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

##### MrExcel MVP, Moderator
.. 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.

Replies
3
Views
488
Replies
2
Views
421
Replies
3
Views
246
Replies
7
Views
372
Replies
1
Views
368

1,195,712
Messages
6,011,266
Members
441,598
Latest member
chrispaulpearce

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back