0

I have a table that I convert to an array in VBA. I used this array to find and replace a list of data I have. What I do is to manually sort my table using the character length of the first column (using len function of Excel) before I run my whole code, which works just fine. However, I want to implement it in such a way that the array would internally sort (by the character length of the first column) in the VBA code so that I don't have to sort it manually and any additional entry wont have to be resorted as well. Do you know any function (or developed function like the Quicksort here) that I can use?

This is the part of my code that converts my Table to an array (in a column array format). I plan to use the function after putting it inside the myArray variable. Thanks for the help in advance.

'Create variable to point to your table
  Set tbl = Worksheets("Sheet2").ListObjects("Table1")

'Create an Array out of the Table's Data and sort it by length
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
Nail. G
  • 35
  • 6
  • If you find any existing VBA code which sorts a 2D array (plenty of examples out there) then you likely only need to replace one line - eg where the two "lines" are compared using something like `If a > b Then` you would replace that with `If Len(a) > Len(b) Then` – Tim Williams Sep 09 '20 at 16:34
  • Thanks @TimWilliams, will try to check it out. If you can also point me to a code that sorts a 2D array, I would highly appreciate it. – Nail. G Sep 13 '20 at 03:55
  • https://stackoverflow.com/questions/4873182/sorting-a-multidimensionnal-array-in-vba – Tim Williams Sep 13 '20 at 05:31
  • Hi @TimWilliams I am bit at lost how to implement it in the link you sent. Can you help me identify which codes to replace using the Len formula? Thanks – Nail. G Sep 17 '20 at 05:35

1 Answers1

0

Here's a very basic example of a 2D sort by length

Sub tester()
    Dim data
    
    With Range("A1:E10")
        data = .Value
        SortByLength data, 2
        .Offset(0, 6).Value = data
    End With
End Sub


'Sort an array (in place) by item length using bubble sort algorithm
Sub SortByLength(data, colIndex As Long)

    Dim FirstC As Integer, LastC As Long
    Dim i As Long, j As Long, c As Long
    Dim tmp
    FirstC = LBound(data, 2)
    LastC = UBound(data, 2)
    
    For i = LBound(data, 1) To UBound(data, 1) - 1
        For j = i + 1 To UBound(data, 1)
            'compare data length in specified column
            If Len(data(i, colIndex)) < Len(data(j, colIndex)) Then
                'swap rows
                For c = FirstC To LastC
                    tmp = data(j, c)
                    data(j, c) = data(i, c)
                    data(i, c) = tmp
                Next c
            End If
        Next j
    Next i
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125