I was recently researching the sorting of an array. I sat in my reading chair and read through John Walkenback’s excellent Power Programming tome on the subject and did some research online when I came across the brilliant .Net feature System Collections ArrayList. What a find it was as this feature allows you to sort an array and send that array right back into Excel.

On running some test coding I ran into a problem. Excel would not run this line

With CreateObject("System.Collections.ArrayList")

Which was kind of frustrating as this was the active line which kicks the whole process off. A bit more reading and I found a patch which adds the feature to your computer. I downloaded this patch and the code ran fine. The following is the link.

http://www.microsoft.com/en-us/download/confirmation.aspx?id=1639

This is something to remember if the coding does not run smoothly. The following is the code which sorts an array. A point of caution. This coding will sort a list of only unique items. The results of the sorted array are stored in column B.

Sub SortInArray() 'System Collections Arraylist, Sort an Excel VBA Array.
Dim ar As Variant
Dim var As Variant
Dim i As Long

   ar = [a11:a20].Value
   With CreateObject("System.Collections.ArrayList")
      For Each var In ar
         If Not .Contains(var) Then .Add var
      Next
      .Sort
      For i = 0 To .Count - 1
         Range("B" & i + 11) = .Item(i)
      Next
   End With
End Sub