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

[DocAElstein]

VBA Range.Sort with arrays. Alternative for simple use

.

I am considering a fairly simple VBA Array alternative approach to a simple use of the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/off...cel.range.sort

In an important file of mine, I currently use the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/off...cel.range.sort
It lets me sort some long rows of data where the order is based on the values in one of the columns , then for any rows which have the same value in that column , those similar rows would be further sorted by another column. For the VBA Range.Sort Method the syntax terminology would say that I am using pseudo code of this sort of form
MyRange.Sort Key1:=use column1 , __ , Key2:=use columnx , __ , __ , __ ,
You can use up to 3 "Key 's" in the simple single code line.
In other words, if you have some identical values in the columns used to define the final list order, you can sort further using the values in a second column to determine the order in the group of identical values in the first column which you used. In the simple single line use of the available VBA Range.Sort Method, you can do that one more time, so 3 times in total .##
In other words, in that single code line it does , pseudo:
Sort by a column, x: (Key1=x)
Sort by a second column, y (Key2:=y), for those rows with duplicates in it in column x
Sort a final time by a third column, z (Key3:=z), for those rows with duplicates in it in both column x and column y

Here is a simple graphical illustration of what I am talking about: Consider this spreadsheet range:
Before:-
_____[/color ( Using Excel 2007 32 bit )

Row\Col	G	H	I	J	K
13	Was G13	C	Was I13	Was J13	Was K13
14	Was G14	a	Was I14	Was J14	Was K14
15	Was G15	g	Was I15	c	e
16	Was G16	g	Was I16	b	Was K16
17	Was G17	g	Was I17	c	f
18	Was G18	G	Was I18	c	Was K18
19	Was G19	f	Was I19	Was J19	Was K19

Worksheet: Sorting
I will run this simple procedure, ( which is in a normal code module. When I run the procedure the worksheet with Name "Sorting" is active).

Sub RangeSortExample()
 Range("G13:K19").Sort Key1:=Range("G13:K19").Columns("B:B"), Order1:=xlAscending, Key2:=Range("G13:K19").Columns("D:D"), order2:=xlAscending, Key3:=Range("G13:K19").Columns("E:E"), order3:=xlDescending, MatchCase:=False
End Sub

Here is a break down of what that routine does:
The first key ,
Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending , MatchCase:=False
, results in this
_____ ( Using Excel 2007 32 bit )

Row\Col	G	H	I	J	K
13	Was G14	a	Was I14	Was J14	Was K14
14	Was G13	C	Was I13	Was J13	Was K13
15	Was G19	f	Was I19	Was J19	Was K19
16	Was G15	g	Was I15	c	e
17	Was G16	g	Was I16	b	Was K16
18	Was G17	g	Was I17	c	f
19	Was G18	G	Was I18	c	Was K18

Worksheet: Sorting
The above screen shows that we have all ordered ( based on the column 2 ,( "B" columns ), of that range , ( which is the column H of the spreadsheet) ). But note, we have duplicates in column 2 with values of g in the last 4 rows.
This next part ,
Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, MatchCase:=False
, then sorts those last 4 rows using column 4, (columns "D") of that range values. ( This is the spreadsheet columns "J")
_____ ( Using Excel 2007 32 bit )

Row\Col	G	H	I	J	K
13	Was G14	a	Was I14	Was J14	Was K14
14	Was G13	C	Was I13	Was J13	Was K13
15	Was G19	f	Was I19	Was J19	Was K19
16	Was G16	g	Was I16	b	Was K16
17	Was G15	g	Was I15	c	e
18	Was G17	g	Was I17	c	f
19	Was G18	G	Was I18	c	Was K18

Worksheet: Sorting
In the above screen shot we see that we now have 3 rows containing all gs in the second column, and all cs in the forth column.
The final term is, noting that we are using xlDescending (just for fun ) ,
Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending
So the last 3 rows are resorted such as to give in those last 3 rows a descending order in the values in the 5^th column in those last 3 rows:
_____ ( Using Excel 2007 32 bit )

Row\Col	G	H	I	J	K
13	Was G14	a	Was I14	Was J14	Was K14
14	Was G13	C	Was I13	Was J13	Was K13
15	Was G19	f	Was I19	Was J19	Was K19
16	Was G16	g	Was I16	b	Was K16
17	Was G18	G	Was I18	c	Was K18
18	Was G17	g	Was I17	c	f
19	Was G15	g	Was I15	c	e

Worksheet: Sorting

Notes:
For simplicity I chose to be case insensitive ( so like g = G __ - _ MatchCase:=False )
Some other argument options are available with the available VBA Range.Sort Method. I will not consider those.
## Furthermore, If you add the options on other lines then use a .Apply code line to apply the sort, then you can have more than 3 "Keys"

I am only considering a comparison to the simple single line using minimal options similar to the worked example

Why do an array alternative?.
I cant think of a particularly good reason: The VBA Range.Sort Method appears to be regarded as a good way to do these things, and often a solution for sorting arrays is based on pasting into a worksheet Range , then using the Range.Sort on that and then capturing the sorted range back into the original range
But I was just interested, out of general interest, to have an alternative: it often occurs that you find bugs in Excel things. The more you have control of "what is going on" , as you do with an array approach, the less is "hidden" from you, such that you have a better chance to change something id something goes wrong.

Various sorting methods, mathematical ways to sort..
The various methods of sorting seem immense and need a good understanding of advanced mathematics. I can't begin to discuss any of that. I will use exclusively the simply "Bubble sort method" , which I will explain from the simplest form, and then adapt to our case

The next posts will go through the simplest Bubble sort theory as applied to a simple 2 dimensional array, and then progress in the following posts to a Function to do similar to the simple case of a VBA Range.Sort Method with the possibility to add keys, for sorting further when values in the initial column used for sorting are identical
For ease of explanation I will refer to the first dimensions in an array as the rows, and the second dimensions as columns , pseudo such that an array could be thought of as a spreadsheet of values. For example a 2 dimensional array of 5 x 2 could be pictorially considered as 5 rows x 2 columns:

r1, c1	r1, c2
r2, c1	r2, c2
r3, c1	r3, c2
r4, c1	r4, c2
r5, c1	r5, c2

[DocAElstein]

My simple sort idea, Simple Bubble Sort
The basic idea as far as I can tell is like , consider this example, for simplicity I will consider
In the procedures below, the first part , Rem 0 , will typically be just some way to get an Array of data for demonstration purposes. They are not part of the main theme of sorting. Also I use WsS just to be sure I am using the correct worksheet in my File. Those can be removed if you have the coding in a normal code module and want the coding to work on the active worksheet that you have "in front of you" , in other words the one that you are "looking at".
Alternatively, 'Comment out all references to the worksheet and test range, Rng, and use the code line alternative to capture the range you have selected.
__ Set RngToSort=Selection ' Selection.JPG: https://imgur.com/HnCdBt8
( We could sort the inputted array, arrTS() , and re paste that out. I have chosen to make a copy of the original array, arrOut() and sort that . I cant think of any reason to do that off the top of my head, other than maybe, in a final code you would still have a copy of the original unsorted data)
Also, Rem 2 is not part of the main demo. This simply pastes out the sorted array next to the original inputted range for easy comparison

Here we go….
Rem 1 Simple Bubble Sort ' =========="Left hand"====Outer Loop==
Simple worded description
Quick Explanation
_We take each row in turn. Start at row 1. Put the value from it "in our, (say left) hand". Actually "in our hand" just means the value at any time in the array element corresponding to the row we are looking at.
___'---------------"Right Hand"----Inner Loop-------
___We then go along all the other rows, (say point at them with our right hand). We look at those next rows in turn, and if we find that the row value "in our (left) hand" is bigger than a row we are pointing at with our right hand, we swap so that we have the smaller "in our (left)hand". Finally "in our (left)hand" will be the smallest. The first time we do this , it will mean that finally the value in the array element corresponding to the first row will have the smallest value in it.
(Remember "in our (left)hand" is the value at any time that is in the ( first initially), row. That value might be change at one or more occasions because it was bigger than the one we compared it with)
We then move on to the next row (with out left hand), and do a similar thing, so that will end up causing the value in the array element corresponding to the second row having the next smallest value in it. So finally the order looking down will be from smallest to largest.

Another "Hand"y way to explain
As it is a tricky concept to get the first time around I will say exactly the same again, just slightly differently:
Say I have a few rows with different values in them .. Grab the first row value in the left hand. Or rather hold it at the first row. Grab the second row value in the right hand, or rather hold it at the second row in the right hand. If the value at your left hand is > than that at the right then swap the values. Your left hand stays at the first row. Your left hand might be holding the original value or a different smaller value.
The right hand should now "let go" of the second row value, and move one row down to hold the third row value. If the value at your left hand is > than that at the right then swap the values. Your left hand stays at the first row.
The right hand should now "let go" of the third row value, and move one row down to hold the forth row value. If the value at your left hand is > than that at the right then swap the values. Your left hand stays at the first row.
As you continue doing this it will result in the smallest value ending up at your left hand, which is at the first row.
Once all rows have been looked at by your right hand, then:
Row one needs no more looking at , as it has the lowest value.
So move your left hand down to row 2, and move your right hand down to row 3.
Your left hand now stays at row 2, whilst your right hand move down all the rows. At each row the values at both hands are compared. If the value at your left hand is > than that at the right then you swap the values.
As you continue doing this it will result in the second smallest ** value ending up at your left hand, which is at the second row. (** Or if you like next number up in value from the smallest will end up at the second row ).

Swapping wth a computer
Inside any sort of coding or this sort of sorting you will need to do that swapping of two values. As far as I know, no coding can swap two things simultaneously

[DocAElstein]

Minor Modifications advancements to simple Bubble Sort Routine

Change Ascending/Descending
For the simple example so far we chose Ascending order, which means that the things "get bigger as you go down the rows"
To reverse this to descending so that things "get smaller as you go down the rows", you simply need to change
the > to a < in the array routine
and
the Order1:=xlAscending to Order1:=xlDescending in the VBA Range.Sort routine
http://www.excelfox.com/forum/showth...0979#post10979

I intended developing the solution into a function, so as a start to this, the routine will be modified to take an Optional argument of 0 or 1 , with the default of 0 being the case for an Ascending list. I am not being particularly efficient with the coding, and will duplicate sections thus

Rem 1 Simple Bubble Sort
Dim rOuter As Long ' ========"Left Hand"=====================Outer Loop=====================================
    For rOuter = 1 To UBound(arrTS(), 1) - 1 ' For row 1 to the (last row -1)  last row, given by the first dimension upper limit of the array
    Dim rInner As Long ' -------Inner Loop-------------"Right Hand"--------------------------
        For rInner = rOuter + 1 To UBound(arrOut(), 1)
            'If arrOut(rOuter, Clm) > arrOut(rInner, Clm) Then           ' This means that I am bigger than the next. So I will swap . I keep doing this which will have the effect of putting the smallest in the current rOuter. By the this and all next rOuter, I miss out the last, and any previous, which means I effectively do the same which puts the next smallest in this next rOuter.
            If GlLl = 0 Then ' We want Ascending list
                'If UCase(CStr(arrOut(rOuter, Clm))) > UCase(CStr(arrOut(rInner, Clm))) Then
                If UCase(CStr(arrOut(rOuter, Clm))) > UCase(CStr(arrOut(rInner, Clm))) Then
                Dim temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
                Dim Clms As Long '-------| with the condition met  a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
                    For Clms = 1 To UBound(arrOut(), 2)
                     Let temp = arrOut(rOuter, Clms): Let arrOut(rOuter, Clms) = arrOut(rInner, Clms): Let arrOut(rInner, Clms) = temp
                    Next Clms '----------| for each column in the array at the two rows rOuter and rInner
                Else
                End If
            Else ' GlLl is not 0 , so presumably we want Descending list
                If UCase(CStr(arrOut(rOuter, Clm))) < UCase(CStr(arrOut(rInner, Clm))) Then
                'Dim temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
                'Dim Clms As Long '-------| with the condition met  a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
                    For Clms = 1 To UBound(arrOut(), 2)
                     Let temp = arrOut(rOuter, Clms): Let arrOut(rOuter, Clms) = arrOut(rInner, Clms): Let arrOut(rInner, Clms) = temp
                    Next Clms '----------| for each column in the array at the two rows rOuter and rInner
                Else
                End If
            
            End If ' End of Ascending or Descending example
        Next rInner ' ---------------------------------------------------------------------
    Next rOuter ' ===========================================================================================
Rem 2 Output for easy of demo

Here is a complete routine with calling procedure: http://www.excelfox.com/forum/showth...0981#post10981

Text or Numbers
It appears from the example tried, that the VBA Range.Sort routine recognizes numbers as numbers and then sorts as we might expect numbers to be sorted.
We can modify slightly our array routine to give similar results.
This is done by doing a comparison based on looking at numbers if at any time the two values under consideration can be seen by VBA as numbers.
The IsNumeric() function is used. If both values are seen to be numeric then they are compared as numbers. This would probably happen from a simple comparison of the values, but just to be on the safe side, we do an extra convert to Double via CDbl( ) in this sort of form
___ If IsNumeric(arrOut(rOuter, Clm)) And IsNumeric(arrOut(rInner, Clm)) Then ' Numeric case
_____ If CDbl(arrOut(rOuter, Clm)) > CDbl(arrOut(rInner, Clm)) Then

Once again, I am not coding particularly efficiently , and so I duplicate sections
Here is a complete routine with calling procedure: http://www.excelfox.com/forum/showth...0982#post10982
Here are some example results: http://www.excelfox.com/forum/showth...0983#post10983


The next post will make a few minor simplifications to develop further a general purpose Function.

[DocAElstein]

Conversion to Array sort function

From now on, I am making up a lot of this as I go along, so I doubt it will bee the most efficient.

General considerations.

_ Always reference the same array
My long term plans on the final version of this is that the routine will work in some recursion process, that is to say , copies of the function will be going further and further to sort further for rows where the last sort left some duplicated values in the column used to sort by. Those copies of the Function would be called from the function itself. That is the simple explanation of hat recursion is about , as applied to VBA Functions: the function starts, then it pauses, whilst it set off another run of the Function to go off and do something similar to what the first function was doing.
One thing that is always a bit awkward to do is pass the array being sorted through the different copies of the function. So what is normally done, and what we will do is have an array outside the function, ( either in an initial calling routine or at the top of the code module )
In the signature line , ( declaring first line) , of the Function it will be defined as being taken ByRef This means that within the Function we refer specifically to that array. This means that changes made in that Function will be reflected directly by those changes into the array we have outside: To a first approximation we can think of it as thought we physically take the array into the function and do stuff to that array, then put it back outside when we have finished. ( The alternative way that we will not use would be to take the array into the function , ByVal , as its name suggests, takes the values of the array at the time into a copy variable , ( sometimes referred to as a local copy variable ), and all is done inside the function on that copy variable , which ceases to exist when the function Ends. The values inside the original array outside are not effected in the ByVal case.

Simple pseudo code examples of difference between ByRef and ByVal:

Pseudo code ByRef ' ( Usually default option )
varMyArray = x
_ Call ReferToIt(varMyArray)
Sub ReferToIt(ByRef arr)
_ arr=y ' This is similar to saying varMyArray = y
End
varMyArray is now = y ' because effectively varMyArray was in arr

Pseudo code ByVal
varMyArray = x
_ Call TakeValue(varMyArray)
Sub TakeValue(ByVal arr)
_ arr=z
End
varMyArray is still = x ' effectively just a copy of varMyArray was in arr , and this copy was thrown away at the End of the routine. So nothing done to arr could have any effect on varMyArray in the ByVal case



Sort only consecutive rows
I am anticipating wanting to sort a number of consecutive rows in the array. On the first run this will be all rows, like for a 5 row array _ 1 2 3 4 5 if the
If the sort based on the values in an initial column found that the third and forth row had a similar value in that column, then a sort based on another column would need in comparison 3 4.
So I am suggesting a strategy could be to have the function do the sort routine based on consecutive rows given in some way, such as in a text string.




The next posts will look at implementations of the general ideas above

[DocAElstein]

Transformation to a Function or Pseudo Function Sub routine

The transformation to a Function we can do easily in two simple steps.
Step 1 The routine is changed so that it takes the array to be sorted into it
The calling routine and main routine here http://www.excelfox.com/forum/showth...0987#post10987 are almost the same as the previous. The only difference is that the array to be sorted, is now outside the main routine and is taken into the main routine at the main routine signature line
Sub SimpleArraySort4(ByRef arrTS() As Variant, Optional ByVal GlLl As Long)
( A few unused 'commented out lines have also been removed )

Because we are using ByRef , the previous testieing Calling routine can also use the original supplied array, arrTS() , after the main procedure Call , provided that the array taken in at the signature line is that sorted. If this is the case, then , in effect , the array taken in and the array in the routine are the same array, and changes done to the array in the function will be reflected in the array outside.
We discussed this previously. Just to recap:
Pseudo code ByRef ' ( Usually default option )
varMyArray = x
_ Call ReferToIt(varMyArray)
Sub ReferToIt(ByRef arr)
_ arr=y ' This is similar to saying varMyArray = y
End
varMyArray is now = y ' because effectively varMyArray was in arr

Often in learn material the basic difference between a simple Function procedure and a Sub routine procedure is said to be that a Function returns a value. In fact , as we see from above, the use of ByRef actually allows us effectively to return a value from a simple Sub procedure.

In the version Sub TestieSimpleArraySort4b() and Sub SimpleArraySort4b(__) here , http://www.excelfox.com/forum/showth...0988#post10988 we are using the effect of ByRef to give us a pseudo Function : To achieve this pseudo Function status, all the array references within the routine are changed to that variable taken into the routine at the signature line.
( This variable name can be anything, I will use arsRef() from now on for all references in the main routine). Doing this allows us to remove all the demo range code lines from the procedure: They can be placed outside and then we can use the array which we declare initially outside the routine, ( arrTS() ) , both before and after the procedure Call: Before it is used to feed the data to be sorted, and After the Call it had been filled with the sorted results , so can be used to paste out the sorted results alongside the original range to compare for demo purposes. To all intents and purposes, arrTS() can be considered as arsRef() and visa versa. This characteristic has been achieved by the use ByRef: Any use of arsRef() is simultaneously Referring to



Step 2 Function instead of Sub ???
I am doing this for completeness. But because I am doing things ByRef I don't need to do this. I may not use it finally
Often in learn material the basic difference between a simple Function procedure and a Sub routine procedure is said to be that a Function returns a value.
This can be explained as follows.
The signature line differs from a normal Sub routine in that it has the form similar to a Dim declaration code line. Consider such a simple Dim line:
_ Dim __ MyThing __ As Variant
A signature line for a Function could look like
Function MyThing(Arg) _ As Variant
In both cases you effectively have a variable of the specified type. The way you fill the variable is slightly different. In the simple case you fill it with something
__ MyThing = "42"
For the function you need to Call it to fill it, and unless the Argument is optional, then you must give it
_ MyThing_ "42"
or
_ Call MyThing("42")
Because the function usually ( you can decide, it does not have to !!!!! ) returns a value in a variable of the type you specify , the most typical use would look like
MyOtherThing = MyThing("42")
The returned value is then put in the variable MyOtherThing

At the end of the day, the main reason for using a function is given as that of returning a value of a specific type.
( By the way, once you have such coding written in a File , then in addition to using it in VBA,,then you could sometimes use it in a cell: Writing = MyThing("42") would often work to return you a value in the cell , resulting from what your function MyThing did with "42" )
If we declared our function as type Variant at the signature line, then that would allow us to return our sorted array, since a Variant variable can hold an array.

The reason why I may not finally use this is that by virtue of using ByRef, my Sub routine will also effectively return that array.
Within the Function itself, the Function MyThing, is like an unfilled variable , in this case a Variant variable. As it is unfilled it will not return anything. !!!!! So within the function, typically towards the end once we have finished doing all we wanted to do, we would have a code line like
Function MyThing(Byref ArrToSort() , ByVal aArg, Optional ByVal GlLl As Long) As Variant
' arrOut() = ArrToSort()
' Do stuff like sort the array
_ MyThing = arrOut()
End Function
The function is then "filled" with what it would return when called

Transformation of pseudo Function Sub SimpleArraySort4b(ByRef arsRef() As Variant, Optional ByVal GlLl As Long) to Function SimpleArraySort5(ByRef arsRef() As Variant, Optional ByVal GlLl As Long) As Variant

Let us note once more that this final step is not necessary: The last procedure fulfils all are current requirements of taking an array and sorting that array, and effectively giving it back to us. It does this by virtue of the array being taken ByRef
The true Function way is more conventionally used so I am doing it just for convenience

The convention way to use the Function would be to arrange that our sorted array is returned by the function. As our current routine stands we only need to add one code line to do this. This code line, placed towards the end of the Function would be
__SimpleArraySort5 = arsRef()
To explain:
The signature line .._
Function SimpleArraySort5(_____) As Variant
_.. Has a very similar effect to this line:
_ Dim _ SimpleArraySort5 _ As Variant
So SimpleArraySort5 could be thought of , to a first approximation , as a pseudo variable
The only difference is that when VBA ( or Excel following a = , like in = SimpleArraySort5 , in a cell ) "sees" SimpleArraySort5 , it will go off and do whatever is inside the function. At the End of the function, the value inside the pseudo variable will be empty still. If we do nothing else then the Function can be Called in exactly the same way that a normal sub routine can
_ Call SimpleArraySort5(___)
However, filling the pseudo variable , for example via code line SimpleArraySort5 = arsRef() , allows us the extra possibility of a code lines like
_____ = = SimpleArraySort5(____)
_ MySortedArray= SimpleArraySort5(MyUnsortedArray)
The type chosen in the declaring signature line must be compatible with what you are doing. In our case, we want to return an array. The only variable type that can hold/ pass an array is a Variant. Hence we chose Variant in this case

( because VBA works generally backwards when reading a code line, we could, in our case , use the same variable such
_ MyUnsortedSortedArray= SimpleArraySort5( MyUnsortedSortedArray )
_ arrTS() = SimpleArraySort5( arrTS() ) )

In the routines
Sub TestieSimpleArraySort5() and Function SimpleArraySort5(______) As Variant
http://www.excelfox.com/forum/showth...0989#post10989 , the main difference over the previous routines is the extra As Variant at the signature line, and finally a code line just before End Function of SimpleArraySort5 = arsRef()

In the testieing routine, we use codes line of this form in the conventional way in which a function is typically used.
_ arrTS() = SimpleArraySort5(arrTS(), _ 0 _ )
But we note that by virtue of using ByRef a simple call would surfice
_Call SimpleArraySort5(arrTS(), _ 0 _ )

Note: we have added an extra testing code section '2b)
In this extra section we fill a new array, arrDesc() , with the sorted array in Descending order. We use for demo purposes a typical function using code line
_ arrDesc() = SimpleArraySort5(arrTS(), 2136)
Correspondingly we have a demo output giving code line
_ RngToSort.Offset(0, RngToSort.Columns.Count * 3).Value = arrDesc()

We note further, that this is somewhat redundant. This is because the code part SimpleArraySort5(arrTS(), 2136) has the effect of re filling arrTS() with the newly sorted array by virtue of the use of ByRef in the signature line of the Function
We could therefore simply use a code line like _..
_Call SimpleArraySort5(arrTS(), 357)
_.. followed by an demo output giving line of
_ RngToSort.Offset(0, RngToSort.Columns.Count * 3).Value = arrTS()





We mow have a simple Array Bubble sort function
The next posts will develop ideas to allow extending the function to allow for sorting further using values from other columns for rows where duplicate values were in the column used initially
I will likely start from the "pseudo" Function
Sub SimpleArraySort4b(ByRef arsRef() ______)

[DocAElstein]

Final Bubble Sorting in Arrays using multi columns for sort values

The post develops ideas to allow extending the function to allow for sorting further using values from other columns for rows where duplicate values were in the column used initially
The start point is the "pseudo" Function
Sub SimpleArraySort4b(ByRef arsRef() ______)

Basic strategy
I will try to develop a recursion routine
It will have two main parts
Rem 1 Bubble Sort
This will be very similar to that code section in all the routines discussed so far. It is intended to sort a set of sequential rows in an array, the order determined by sorting values from a specific column. Initially it will be all rows in the array. There after it will work again but only on those rows which had identical values in the column chosen to sort by. As these duplicate rows will be sequential, it is just a case of the Bubble sort being done at any time over a specific sub set of the original rows.

Rem 3 determine duplicate rows range
This new section will work after every sort to determine the duplicate value rows. It will give us a list like " 4 5 6 " . This is the rows we organise Rem 1 to sort again using a different columns values.


Working Implementation
I propose some arguments , where possible similar to those in the VBA Range.Sort method , to aid in comparison along the lines of the VBA Range.Sort arguments and syntax for the single line case.
I will therefore change the signature line to this form

Sub SimpleArraySort6(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)

CpyNo : I want to use a recursion idea. The idea is that I "keep going" sorting by the values in different columns until I have an ordered list , like
A b D
A B e
A x 2
B w 4
B y z
f m h
F m m
f O t
In that simple example, duplicates in the first ordered column, are sorted by the second column, and the remaining duplicates ( the f m 's ) sorted by the final column. ( In this example all sorting is done in Ascending order )
To determine which column I am working on I will need to access the corresponding number in a list. This is the sort of thing that it would be useful to have the variable CpyNo for: In any recursion coding it is almost always useful to know which "level" you are in or "how many levels down" or , in terms of what is actually going on , which copy of the routine you are in.
Recursion is when a routine pauses, then another copy of the routine is started. This could continue for a number of "levels" or copies. I that last example we would have had finally 3 copies open at the same time.
This concept is very difficult to explain easily. Here is a simpler worked example ahdAHDHDLHDLKHLKHL

arsRef() : This is used exactly as in all our codings. It holds our entire array in whichever order it currently is. Each "level" or "recursion routine copy" corresponds to sorting by a specific column, and the array arsRef() past to be updated from one level to the other.

strRws : This looks like this initially " 1 2 3 4 5 6 7 " , but then as we identify duplicate rows in the last sort, it will change to like " 4 5 6 " which will then go through a similar sorting process and then might reveal other rows to further sort , such as in that example it might change to " 5 6 "

strKeys : In the VBA Range.Sort method , we discussed the arguments as being like pseudo_...
Range("A1:F10").Sort Key1:=column 2 , order1:=Ascending, Key2:=column 3 , order2:=Descending, ……
_.. so … I propose a simple string of this form
" 2 Asc 4 Asc 5 Desc ……….."
The number is the column number of the range to use in a sort "level" and the text determines ascending or descending order in the final sorted rows. So in that example arguments, the entire rows of the array are sorted based on the value in the second column, and if there are rows containing the same value in column 2, then those rows will be sorted using the values in column 4 .. etc…

Full routines walkthrough
In any use of the routine, it will need to be Called an initial time from another routine. At the Call line you will need to give the initial unsorted array , all rows for that array as a series of sequential integer numbers, your chosen key parameters , and a copy number of 1 for the recursion "copy" or "level".
Demo coding is given in Rem 1 of Sub TestieSimpleArraySort6() . This is just for demo purposes and is not part of the main recursion procedure issue.

Main recursion routine
Sub SimpleArraySort6(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
http://www.excelfox.com/forum/showth...0994#post10994
The "copy number of the recursion" or "level down" should be supplied as 1 by the first Call, as is done in the demo testing code, Sub TestieSimpleArraySort6()
For any subsequent Calls of this recursion routine, the Calls are made by the routine itself. This is what happens in a recursion routine. ( This is why a recursion routine is often described as a routine that Calls itself. I personally find this miss leading. I would prefer to say that the routines pauses whilst it sets off another copy run of the routine. This other copy is then a separate routine, a new copy, which is completed before the original copy resumes. )
For any subsequent Calls of this recursion routine, the value supplied to the routine at the Call line is given as CpyNo +1. This results in the number being taken in the new independent copy variable of CpyNo as one greater for every "new copy" or "next level down" copy of the recursion routine. Hence this variable can be used to indicate "where we are". In our example it tells us which of the columns we are sorting by: This is because, as another copy is set off, it is done so in order to sort some duplicate sequential sub rows which were found by the last sort: When this occurs we want to take the next column in our supplied sort keys to try to order these sub rows.

Rem -1 from the supplied arguments, get all data needed in current bubble sort
I make an array from the supplied string of sort info argument , (strKeys _ = " 2 Asc 5 Desc 6 Asc….." based on a split by " " ( also allowing for any extra spaces like " 2 Asc 5 _ Desc 6 Asc….." ) )
In conjunction with the CpyNo I am then able to pick out the relevant column number and sort order info to suit the copy of the recursion routine currently running.
Finally in this section, I make a 1 D array from the supplied string of sequential row numbers " 3 4 5 6 ". I only need the upper and lower numbers in further calculations and lootings, , which I get later from the upper and lower limits of the array. ( I note that I could just as well do that form some string manipulation )

Rem 1 Simple Bubble Sort
This is almost exactly the code section used in the last few routines. The main difference is that the upper and lower row limits are those supplied in the strRws argument ( like " 3 4 5 6 " ) rather than the first row and last row of the full initial array to be sorted. By the first Call of the recursion routine, these will in fact be the first row and last row of the full initial array to be sorted. By any subsequent copy runs of the recursion procedure these will typically be some sub set of sequential rows that need to be resorted as the first sort had some rows where the value in the column used to base the sort on had the same value.

' Rem 3 Determine any duplicates in sort column values , and re run the routine to sort them by another column
This is the main new part which includes the recursion causing bits, so it is not going to be easy to explain or follow. I will try..
It is not easy to explain as it is all sort of mixed up: it is not easy to separate into bits that can easily be explained on their own.
At any time in this code section our current row is determined by rOuter. This variable had been used for the "outer loop" or "left hand" in the Bubble sort of Rem 1 Here it is doing something similar: keeping track of where we are as we go down the entire rows just sorted. We go down the entire rows just sorted to try to find a sub set of sequential rows with the same value in the column we used to sort by.
We use the variable strRws as we always do to hold our the indicies of a set of sequential rows. So we empty it (strRws = "" ) before we start.

We have a Loop **** for all the rows under consideration, ( actually we only loop to one less than all rows, as we look at the current row and the next one inside this Loop)
At each row we look to see if we have started a string of sequential rows and if not we start one at the current row
Then we look to see if in the sort column the next row is a duplicate, if it is we add it to our strRws. If it is not a duplicate we check to see if we have at this point a set of duplicate values, which will be indicated by if strRws has at least one separating " " in it. If this condition is met then we do the "recursion" ( ' Rec Call 1 ) . VBA recognises what we are trying to do now, so it pauses the current routine, and starts a new copy using the arguments we give it at Rec Call 1
The next copy of the recursion routine will ether sort the rows , or if it can't , the same process will happen again whereby that copy will pause and another will go off to try to sort the unsorted rows using the next column we chose. In any case eventually we should come bck to this original copy, and look further down as there might be another set of rows with the same value in the column used to sort.
That will solve the problem in almost all cases. Unfortunately my logic is a bit messy, and if rows at the end need sorting they got missed. So I had to add a section to take care of that situation. The logic there is similar to that previous


_._________________________________________

Final demo coding is here:

http://www.excelfox.com/forum/showth...0993#post10993
http://www.excelfox.com/forum/showth...0994#post10994
http://www.excelfox.com/forum/showth...0995#post10995



Demo example in next post…

[DocAElstein]

Take an example,
A list of Foods, their name in first column and a few other things like calories(Kcal) and Salt content in other columns

First I want to sort to group similar products (based on alphabetical order, but ascending or descending is not important) - This will be sorting on column 1 values

Within similar food types, I want to list them in an order of how healthy they might be, ( or at least in the order of least unhealthy ) .
Most important would be order starting with lowest Kcal.
After that for similar products with similar Kcal , we would consider the minimum salt content as likely to be the less unhealthy.

This might be our list
_____ ( Using Excel 2007 32 bit )

Row\Col	R	S	T	U	V	W
22	Food Product	Was S22	Kcal	Was U22	Salt	Was W22
23	Crisps	Was S23	500	Was U23	0.7	Was W23
24	Beer	Was S24	200	Was U24	0.1	Was W24
25	Wine	Was S25	150	Was U25	0.15	Was W25
26	Beer	Was S26	200	Was U26	0.07	Was W26
27	beer	Was S27	220	Was U27	0.2	Was W27
28	Beer	Was S28	210	Was U28	0.06	Was W28
29	Wine	Was S29	160	Was U29	0.04	Was W29
30	wiNe	Was S30	150	Was U30	0.03	Was W30
31	Crisps	Was S31	502	Was U31	2	Was W31
32	Onion Ringes	Was S32	480	Was U32	1	Was W32
33	Onion Ringes	Was S33	490	Was U33	1.5	Was W33
34	Crisps	Was S34	502	Was U34	1.5	Was W34
35	CRISPS	Was S35	500	Was U35	1.1	Was W35
36	Wine	Was S36	170	Was U36	0.1	Was W36
37	Crisps	Was S37	500	Was U37	3	Was W37

Worksheet: Sorting


Here is a demo Calling test routine

Sub TestieSimpleArraySort6()
Rem 0 test data, worksheets info
Dim WsS As Worksheet: Set WsS = ThisWorkbook.Worksheets("Sorting")
Dim RngToSort As Range: Set RngToSort = WsS.Range("R23:W37")
' Set RngToSort = Selection '                          ' Selection.JPG : https://imgur.com/HnCdBt8
Dim arrTS() As Variant: Let arrTS() = RngToSort.Value ' We would have to use  .Value  for a range capture of this sort because  .Value  returns a field of Variant types.  But also at this stage we want to preserve string and number types
' Call SimpleArraySort6(1, arrTS(), " 1 2 3 4 5 ", " 1 Asc 2 Asc 3 Asc")
Dim cnt As Long, strIndcs As String: Let strIndcs = " "
    For cnt = 1 To RngToSort.Rows.Count
     Let strIndcs = strIndcs & cnt & " "
    Next cnt
Debug.Print strIndcs ' For 5 rows , for example we will have  " 1 2 3 4 5 " , for 15 rows  " 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 "
 Call SimpleArraySort6(1, arrTS(), strIndcs, " 1 Desc 3 Asc 5 Asc")
Rem 2 Output for easy of demo
' 2a
 RngToSort.Offset(0, RngToSort.Columns.Count).Clear
 Let RngToSort.Offset(0, RngToSort.Columns.Count).Value = arrTS()
 Let RngToSort.Offset(0, RngToSort.Columns.Count).Interior.Color = vbYellow
' 2b VBA Range.Sort Method equivalent
Dim TestRngSrt As Range: Set TestRngSrt = RngToSort.Offset(0, RngToSort.Columns.Count * 2)
 TestRngSrt.Clear
 Let TestRngSrt.Value = RngToSort.Value
 TestRngSrt.Sort Key1:=TestRngSrt.Columns("A:A"), order1:=xlDescending, Key2:=TestRngSrt.Columns("C:C"), order2:=xlAscending, Key3:=TestRngSrt.Columns("E:E"), order3:=xlAscending
 TestRngSrt.Interior.Color = vbGreen
End Sub
'

That above routine uses the test range R23:W37 above and feeds that to the main recursion routine ( http://www.excelfox.com/forum/showth...0994#post10994 )











Full demo example is shown here
Final demo coding is here:
http://www.excelfox.com/forum/showth...0993#post10993
http://www.excelfox.com/forum/showth...0994#post10994
http://www.excelfox.com/forum/showth...0995#post10995

some more notes here
http://www.eileenslounge.com/viewtop...245517#p245517



Ref
https://stackoverflow.com/questions/...n-four-columns

[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

[DocAElstein]

I am thinking of an alternative approach, the idea being to reduce on the steps to reorganising the array at every swap stage.. the idea came from 2 things …

_1 The recursion routine is fed currently the row indices of the rows that need to be sorted.
_2 We can use the VBA Application.Index Method which allows us to re sort an array “ in one go “ via a code line like , pseudo formula..

arrOut() = App.Indx( ArrIn() , {1;3;2} , {1,2,3} )
arrOut() = App.Indx( ArrIn() , row indices , column indicies )
arrOut() = App.Indx( ArrIn() , Rs() , Cms() )

The above code line would change an ArrIn() like this …_
A b c
D e f
G h I
_ … to a given output in arrOut() like this:
A b c
G h I
D e f

So the idea is that we sort the indices values, and then re apply the formula above

At this stage I propose modifying the existing code so as to have a better chance of a direct comparison in performance.. Both will be then subject to similar general inefficiencies arising from the very opened out explicit form of the codlings generally. I will do two version of this code, Sub SimpleArraySort7( and Sub SimpleArraySort8(
Sub SimpleArraySort7( will add the extra coding, and Sub SimpleArraySort8( will remove some of the now unnecessary / redundant parts thereafter, so as to attempt a good comparison to the previous Sub SimpleArraySort6(
Finally I may make a more trimmed version

Here the basic modifications for Sub SimpleArraySort7(__ , Sub TestieSimpleArraySort7()

Global Variables
To help simplify the comparison and so reduce the changes to the routines, I will have a some Global variables at the top of the module and outside any routine for the row and column indices
Dim Cms() As Variant, Rs() As Variant
This will allow me to refer to, that is to say change and use, in any copy of the recursion routine. (It would also be an alternative place here at the top of the module and outside any routine for our main array, arrTS(): we could then always refer to this, and then not need the ByRef arsRef() at the signature line of the recursion routine. But for now I will leave that as it is for closer comparison of the routines. )
These two “single width” arrays,
_ Rs() , “vertical, rows”
and
_ Cms() , “horizontal columns”
, will hold whole number indices for use in the VBA Index method formula idea.
For the type of the elements of these arrays, the Long type would be OK, and also in such situations VBA usually accepts Sting types that look like an number. The only reason that I use Variant is that I use a convenient way to get the initial indices, and that way happens to return a field of Variant types
To help in the development of this coding and to help with the explanation here, I have also moved the variable for the test range, RngToSort , to the top of the module and outside any routine to make it a global variable: This way I can use multiples of it’s dimensions to position intermediate paste outs of the arrTS(). For example , I have added a section immediately after the end of the main outer loop == for sorting, ' Captains Blog, Start Treck , which pastes out the current state of the sorted array , arrTS(), along with the current state of the indices, Rs()

' Captains Blog, Start Treck
 Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Value = arsRef()
 RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Value = Rs()
 Debug.Print " Running Copy " & CopyNo & " of routine." & vbCr & vbLf & "  Sorted rows " & strRws & " based on values in column " & Clm & vbCr & vbLf & "   Checking now for Dups in that last sorted list" & vbCr & vbLf

For Sub SimpleArraySort7(__ , I will also include a new array variable , as a global variable, arrIndx(). This I will fill by the formula line of
arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
The formula above needs to be applied to the original range, so I also have another global variable which will contain the original range, arrOrig()
The final output, for example from the first passing of this section, can be seen here:
http://www.excelfox.com/forum/showth...ll=1#post11049
In that screenshot the output from the previous routines produced in
__arsRef()
is shown and , for comparison, alongside it is shown that produced by
______________arrIndx() = Application.Index(arrIndx(), Rs(), Cms())

Modifying indices values in main sort loop sorting
In other words, how do we get the modified Rs() to use in
arrIndx() = Application.Index(arrIndx(), Rs(), Cms())
The way the current coding is organised makes this fairly simple. We have sections where all column elements in a row are swapped.
__ For Clms = 1 To UBound(arsRef(), 2)
___ Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
__ Next
We use the row information in the variables rOuter and rInner. So quite simply, we do the same swap for row indices,
Dim TempRs As Long
_ TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs
With those simple modifications we obtain for the final full run the results shown here:
http://www.excelfox.com/forum/showth...ll=1#post11050

Summary
In the next post the actual modifications to transform Sub SimpleArraySort6( to Sub SimpleArraySort7( are walked through in a bit more detail. Here is the brief summary to help in orienteering.
In the previous recursion routines, ( and still in this intermediate version, Sub SimpleArraySort7( ) the entire row values are swapped at various stags in the Bubble sort process. In our codings so far, we had typical sections like this

                Dim Clms As Long '-------| with the condition met  a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
                    For Clms = 1 To UBound(arsRef(), 2)
                     Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
                    Next Clms '----------| for each column in the array at the two rows rOuter and rInner

In that above snippet, every column value in a row goes through the typical three line swap using a temporary variable: __ temp=My1 _ My1=My2 _ My2=temp https://excel.tips.net/T002525_Swapp...o_Numbers.html
For the new coding idea, we only need to swap the row indices so as to change their order from like {1;2;3;4….} to the new sorted order like ModifiedRowIndicies ={2;1;4;3….}, since then we will apply the idea .._
__ arrNext() = App.Indx( originalRange , rows() , columns() )
__ arrNext() = App.Indx(originalRange, ModifiedRowIndicies , {1,2,3,4,…..})
_.. to get the modified row order from the original range. ( The columns, columns() , remain in the original order , {1,2,3,4,…..} )
So we have an extra code line which just swaps the row indicia. ( In this intermediate version I still include the previous swap sections )

                Dim Temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
                Dim Clms As Long '-------| with the condition met  a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
                    For Clms = 1 To UBound(arsRef(), 2)
                     Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
                    Next Clms '----------| for each column in the array at the two rows rOuter and rInner
                Dim TempRs As Long
                 Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs

The main other extra code section is then the code line to get the new row order using the reordered row indices
___ arrIndx() = Application.Index(arrOrig(), Rs(), Cms() )
This done at the end of the sort section

        Next rInner ' ---------------------------------------------------------------------
    Next rOuter ' ===========================================================================================
 Debug.Print "Doing an arrIndx()"
 Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
' Captains Blog, Start Treck

[DocAElstein]

Sub TestieSimpleArraySort7()
Sub SimpleArraySort7(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)

Global variables
At the top of the module in which are codes are we need our global variables

Dim Cms() As Variant, Rs() As Variant      ' "HorizointalColumn" Indicies  , "Virtical row" Indicies
Dim RngToSort As Range                     ' Test data range
Dim arrIndx() As Variant                   ' For modified array at end of each sort of a set of rows
Dim arrOrig() As Variant                   ' This    arrIndx() = Application.Index(arrOrig(), Rs(), Cms())  applies the modified Rs() to the original unsorted data range. So we need an array to use constantly containing the original data range

Sub TestieSimpleArraySort7()
I use the same basic test data range as in other routines in the last few versions of Sub SimpleArraySort7(
Additionally I need the original data range in the separate ( global) array, arrOrig() , to use continuously in the first argument in the code line,
______Application.Index(arrOrig(), Rs(), Cms())
So this takes the value of the test data range
My array of column indicies , Cms() , is conveniently obtained in one go using a spreadsheet function , Column( ) which returns the column numbers of a given range in a horizontal array. I want like {1, 2, 3, 4, 5}, for my 5 column range, so I use something like Column(A:E) , Evaluate(Column(1to6)).JPG : https://imgur.com/jbaZdgJ
Similarly I can obtain the initial required vertical array indicia {1; 2; 3; ………} from a spreadsheet function, Row( ) Evaluate(Row(1to15)).JPG : https://imgur.com/UVTQCYO
' test index
I have a short test section whereby I paste out the row and column indices as well as the original array using these indices, .._
RngToSort.Offset(RngToSort.Rows.Count, 0).Value = Application.Index(arrTS(), Rs(), Cms())
_... which should return the original range for simple ordered indices, ( https://www.excelforum.com/excel-new...ml#post4571172 )
For convenience I paste out the array thus produced directly under the original range for comparison. We find that they are identical , as expected.

Sub SimpleArraySort7(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
For this version we have kept the same coding as for Sub SimpleArraySort7(_
The signature line remains the same, as all the extra variable we need are the Global variables discussed.
In every swap section we add the row indicie swap line ( 3 lines )
TempRs = Rs(rOuter, 1): Rs(rOuter, 1) = Rs(rInner, 1): Rs(rInner, 1) = TempRs
The only extra line needed for the routine to function with the Index method idea is
__arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
This last line is added at just after every sort. We also include in this version a few lines to paste out the stages in the sorting for both the array from the previous method, arsRef() , and that from the Index method idea, arrIndx()

        Next rInner ' ---------------------------------------------------------------------
    Next rOuter ' ===========================================================================================
 Debug.Print "Doing an arrIndx()"
 Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
' Captains Blog, Start Treck
 RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Clear               '   Area for array produced from previous method
 Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Value = arsRef()
 RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), RngToSort.Columns.Count).Clear    ' Area for array produced by Index method idea
 Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), RngToSort.Columns.Count).Value = arrIndx()
 RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Clear
 Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Value = Rs() ' Current indicies order to apply to original range
 Debug.Print " Running Copy " & CopyNo & " of routine." & vbCr & vbLf & "  Sorted rows " & strRws & " based on values in column " & Clm & vbCr & vbLf & "   Checking now for Dups in that last sorted list" & vbCr & vbLf
' Rem 3 Determine any duplicates in sort column values , and re run the routine to sort them by another column

---

Coding here
http://www.excelfox.com/forum/showth...ll=1#post11053
http://www.excelfox.com/forum/showth...ll=1#post11052

and in attached File

---

In the next post , the code is simplified slightly to just do the Index method idea