Sort Elements In Array Excel VBA Macro

In a previous post we have seen how to sort text or words inside a single cell in the Excel worksheet using VBA macros. But that approach worked only with a list of unique values. In this post, we see how to sort the elements in the array created from the cell content, regardless whether that content has unique or repeated values. In both cases, the Split VBA function is used to convert the text or values into an array of elements. In this example, the elements are words separated by a space, but the same method can be used for values separated by a comma, semicolon, or a line break, as in the second scenario of the previous post.

Macro/VBA code:

 

  Sub SortArray()

      Dim arr As Variant, elem As Variant, word As String

 

      arr = Split(Trim(ActiveCell), " ")

      'arr = Array("car", "auto", "mechanic") 'hard-coded

 

      For i = LBound(arr) To UBound(arr)

          For j = i + 1 To UBound(arr)

              If UCase(arr(i)) > UCase(arr(j)) Then

                  word = arr(j)

                  arr(j) = arr(i)

                  arr(i) = word

              End If

          Next j

      Next i

 

      'add sorted array elements to other cell

      For Each elem In arr

          ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2) & elem & " "

      Next elem

 

  End Sub

 

 

Macro explained:

• We declare both the array variable and the variable to store each element in the array while looping as a Variant. We also need a temporary string variable (word) to store the value or text in the array while looping and sorting the elements of the array.
• We use the Split VBA function to get each of the words in the active cell separated by a space. Note that we use Trim to get read of trailing spaces within the cell content. The space can be replaced by any other character such as a comma, semi-colon, etc. If the content is separated by line breaks, we split the text by the line feed (vbLf).
• Then we have two nested loops to go through each of the elements in the array, and sort the elements alphabetically by changing the value of the corresponding position within the array (which comes from the variables i and j). We loop from the lower bound (UBound) to the upper bound (UBound) of the array first, while starting on the next element in the second loop each time. Then we compare the upper case of each value or element and sort the value alphabetically. This is a similar approach to the one used in a previous post to sort worksheets in a workbook.
• Now the array is sorted alphabetically and the values can be added to a single cell or to a range (each element in a different row). In the code example above, the elements are put together in the same cell (situated two cells to the right of the active cell), each of them separated by a space, looping through each element in the array with a For Each loop.

This is how we sort elements in an array in Excel with VBA macros.

Other examples:

• How to Sort Unique Text Values In Cell with Excel VBA macros
  
• How to Process HTTP response to Scrape Web Data with Excel VBA macros
  
• How to Send HTTP request to Scrape Web Data with Excel VBA macros