Returning a sorted list : Function Return « Language Basics « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Language Basics
3. Function Return

Returning a sorted list

 
  =Sorted(A2:A13)

  Function Sorted(Rng As Range)
      Dim SortedData() As Variant
      Dim Cell As Range
      Dim Temp As Variant, i As Long, j As Long
      Dim NonEmpty As Long

      For Each Cell In Rng
          If Not IsEmpty(Cell) Then
              NonEmpty = NonEmpty + 1
              ReDim Preserve SortedData(1 To NonEmpty)
              SortedData(NonEmpty) = Cell.Value
          End If
      Next Cell

      For i = 1 To NonEmpty
          For j = i + 1 To NonEmpty
              If SortedData(i) > SortedData(j) Then
                  Temp = SortedData(j)
                  SortedData(j) = SortedData(i)
                  SortedData(i) = Temp
              End If
          Next j
      Next i

      Sorted = Application.Transpose(SortedData)
  End Function

  =TRANSPOSE(Sorted(A16:L16))

 

Related examples in the same category