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

**DocAElstein** · 03-13-2019, 07:35 PM

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

Code:

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()

Code:

        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