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

**DocAElstein** · 03-17-2019, 07:05 PM

Final conversion of Sub Bubbles to Sub BubblesIndexIdeaWay

Step 2 removal of redundant coding
In the last post we modified indices values in main sort loop sorting
to get the modified Rs() to use in this
arrIndx() = Application.Index(arrIndx(), Rs(), Cms())

What we did was, at this section _...
__ 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 used the row information in the variables rOuter and rInner, to do 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

Column elements in a row swapping
One of the main distinguishing characteristics of the Index idea way, is that we sort the row indices in to a new order, and then apply the code line, .._
arrIndx() = App.Ind(arrOrig() , rowindicis, columnindicies
_.. to get the new order in one go.
However we must be careful. The immediate conclusion might possibly be that all the sections swapping all column elements in a row are now redundant and so can be removed. The is almost true, but not quite: The reordering of the row indicia is following directly the bubbling through sort of the column being used in the current sort. We must therefore continue to sort/ swap this column element e currently have this
____ 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
___ Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs

That needs now to be modified now so that they just swap those two row values in the column currently used to base the sort on, ( as well as still doing the swap of the row indicia )
We note that Clms was the variable for all columns in the loop for all columns in the swapping in the code snippet above , and Clm was the variable for the current column being used to determine the current sort order. So we no longer need that loop to swap all columns, - that can be removed. But if we do this removal, we must add a swap section for the Clm column …_

___ Let Temp = arrIndx(rOuter, Clm): Let arrIndx(rOuter, Clm) = arrIndx(rInner, Clm): Let arrIndx(rInner, Clm) = Temp
___ Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs

The complete bubble loop section now looks like this

Code:

Rem 1 Bubble sort
Dim rOuter As Long ' ========"Left Hand"=====================Outer Loop=====================================
    ' For rOuter = Rs(LBound(Rs(), 1), 1) To Rs(UBound(Rs(), 1), 1) - 1 ' THIS WOULD ONLY WORK FOR Copy No 1      For first row indicie to last but one row indicie - I could do this for copy 1
    For rOuter = Left(Trim(strRws), InStr(1, Trim(strRws), " ", vbBinaryCompare) - 1) To Right(Trim(strRws), Len(Trim(strRws)) - InStrRev(Trim(strRws), " ", -1, vbBinaryCompare)) - 1 '   ImmediteWindowToHelpGetAtStuff.JPG : https://imgur.com/yGqmYSu : http://www.eileenslounge.com/viewtopic.php?f=27&t=16407&p=247121#p247121
    'For rOuter = 1 To 5 ' For first run
    Dim rInner As Long ' -------Inner Loop-------------"Right Hand"--------------------------
        For rInner = rOuter + 1 To Rs(UBound(Rs(), 1), 1) ' from just above left hand through all the rest
            If CDbl(arsRef(rOuter, Clm)) > CDbl(arsRef(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
             Let Temp = arrIndx(rOuter, Clm): Let arrIndx(rOuter, Clm) = arrIndx(rInner, Clm): Let arrIndx(rInner, Clm) = Temp
            Dim TempRs As Long
             Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs
            Else
            End If
        Next rInner ' -----------------------------------------------------------------------
    Next rOuter ' ==================End=Rem 1===============================================================
Rem 2
 Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())

Here is the full final coding for Sub Call_Sub_BubblesIndexIdeaWay() and Sub BubblesIndexIdeaWay(__
http://www.excelfox.com/forum/showth...ll=1#post11074
http://www.excelfox.com/forum/showth...ll=1#post11078

_.__________________________-

The next part of this Thread will be to extend the shortened demo coding from the last few posts to a full coding example.
Effectively this will be a slightly more efficient version of Sub SimpleArraySort8(__
http://www.excelfox.com/forum/showth...ll=1#post11054
http://www.excelfox.com/forum/showth...ll=1#post11056
http://www.excelfox.com/forum/showth...ll=1#post11058

_...... I expect I will do that later as a bit of revision when it snows next winter , and I come back inside to sit more on my bum and do computer stuff… Until then I’m off to do more useful things outside…. See ya x

https://excelfox.com/forum/showthrea...ll=1#post11079

Ref
https://excelribbon.tips.net/T009600...d_Numbers.html
http://www.eileenslounge.com/viewtop...247043#p247043