VBA Range.Sort with arrays. Alternative for simple use.

[DocAElstein]

Example: A few thousand columns with a few dozen rows

A requirement I have is to re sort occasionally a sub set of rows in a large list of very wide rows ( approx 3500 columns ). Typically I have sections of 50 rows which “belong together”, ( 49grouped + 1 below ) , and things may be added. It is easier to add anywhere in the section and then do a sort on the entire group, or part thereof , as required..
49grouped+1.JPG : https://imgur.com/a8rflEn
The Range.Sort method works well, so the array version is just a spare alternative…

Range.Sort method coding.
A full description is given here:
http://www.excelfox.com/forum/showth...ll=1#post11032
http://www.excelfox.com/forum/showth...ll=1#post11033
http://www.excelfox.com/forum/showth...ll=1#post11034
http://www.excelfox.com/forum/showth...ll=1#post11035

Here is just a brief Description of the Range.Sort method coding:
The range to sort, rngToSort , is determined. It is basically taken as the user selected range on the active worksheet in the active window. Because there are a lot of columns, and the number is known ( 1 – 3488 ) , these are hardcode, and the user only needs to make a selection of any columns over the required rows: The selection determines the row range and the column range is hard coded.
An array, ArrrngOrig() , is made of the current range values.
This can be used if it is decided not to accept the sorted range and instead to replace the original range order.
In the Array version alternative this will be the array to be given to the sort routine.
The main code line doing the Range.Sort is

rngToSort.Sort Key1:=wksToSort.Columns("H"), order1:=xlDescending, Key2:=wksToSort.Columns("J"), order2:=xlDescending, Key3:=wksToSort.Columns("X"), order3:=xlDescending

The relevant arguments from there that we need to feed to our array sort routine are therefore
Key1:=wksToSort.Columns("H"), order1:=xlDescending
Key2:=wksToSort.Columns("J"), order2:=xlDescending
Key3:=wksToSort.Columns("X"), order3:=xlDescending
The array to be given can be assigned directly to
ArrrngOrig()

Array sort routine alternative coding( revision of our current coding )
Here is our Main recursion Array sort routine http://www.excelfox.com/forum/showth...0994#post10994
Sub SimpleArraySort6(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
Because this is a recursion routine, it need an initial routine to Call it the first time and feed it the appropriate arguments.
It will need to supply :
CpyNo : This is is very useful for debugging to help tell us which copy of the routine is running at any time. We have seen that this is incremented every time a routine pauses and sets off another copy. The increment is done as the new copy is started, and that incremented value passed to the newly starting routine, so CpyNo will be an indication of the copy running. So this we will need to set to one at the initial Call done by the initial Calling routine to 1
Also it is needed to pick out the correct sort key, in other words to set the copy of the routine to sort based on the correct column: Each further copy of the recursion routine uses the next column of values to be used in the progressive sorting.
arsRef() : We can declare a dynamic Variant array and assign our range capture array , ArrrngOrig() , directly to this. Lets call this arrTS() for consistence to our previous discussions
strRws : This is used to give the indices of the array, ( in a string form ) of all rows currently being sorted. On the initial Call this will need to have all the rows and be of this form “ 1 2 3 4 5 6 ……….. N “. We will need to make this in the Calling routine
strKeys : This takes a form to look similar to the arguments of the Range.Sort method. Because we are looking from column A , our columns follow the spreadsheet columns. All our order is Descending. This would be our basic form required, which will be passed unchanged between copies of the recursion routine. ( We pass this ByValue , but we could just as well uses ByRef for this variable )
In our initial Calling routine we will need a code line of this form
strKeys = “ 8 Desc 10 Desc 24 Desc “

Calling initial routine
The routine is here http://www.excelfox.com/forum/showth...ll=1#post11038
The first 3 section, Rem 0 Rem 1 Rem 2 , and final section Rem 4 are not directly relevant to the sort routine, and are very similar to the same code sections in the Range.Sort routine described here: http://www.excelfox.com/forum/showth...ll=1#post11035
Rem 3
'3a) arguments for Called routine
A simple loop produces our string of all row indices, strIndcs
An array is made containing the initial unsorted range to be passed to the recursion routine, arrTS()
The remaining arguments of recursion routine copy number, CpyNo , abd the keys required, strKeys , are passed hard coded directly in the next section, '3b)
'3b) ( '3c) alternative**) This is the main equivalent to the Range.Sort method
The main recursion routine is Called and after the range is given the array of sorted values
**‘3c) is an alternative which comes closer to resembling the single line Range.Sort method , for comparison:

rngToSort.Sort Key1:=wksToSort.Columns("H"), order1:=xlDescending, Key2:=wksToSort.Columns("J"), order2:=xlDescending, Key3:=wksToSort.Columns("X"), order3:=xlDescending

'3c)
arrTS() = rngToSort.Value: Call SimpleArraySort6(1, arrTS(), strIndcs, " 8 Desc 10 Desc 24 Desc "): rngToSort.Value = arrTS()

here again, pseudo form, for ease of comparison:

rngToSort.Sort
___________Key1:=wksToSort.Columns("H"), order1:=xlDescending,
_________________Key2:=wksToSort.Columns("J"), order2:=xlDescending,
_______________________Key3:=wksToSort.Columns("X"), order3:=xlDescending

‘ 3c)
arrTS() = rngToSort.Value
Call SimpleArraySort6(1, arrTS(), strIndcs,
__________________________" 8 Desc 10 Desc 24 Desc ")
rngToSort.Value = arrTS()


( For a spreadsheet, for the letter across the top,
_____________H is column number 8 , J is column number 10 , X is column number 24 )

Here is the file used do far:
"ProAktuellex8600x2Sort1.xlsm" https://app.box.com/s/d6isabudadt3swnryxiz7motspzeqa17

---

Here are some timing experimants:
http://www.excelfox.com/forum/showth...ll=1#post11041
http://www.excelfox.com/forum/showth...ll=1#post11042
http://www.excelfox.com/forum/showth...ll=1#post11043
http://www.excelfox.com/forum/showth...ll=1#post11045
http://www.excelfox.com/forum/showth...1046#post11046