Use VBA Excel Function to sort data in ascending order

This Excel tutorial shows how to use custom VBA Excel Function to sort data in ascending order instead of using Filter.

You may also want to read

Sort data using custom Function by assigning number to Text

Sort data using Range Sort Method

Sort data using Table AutoFilter

Sort data using Worksheet AutoFilter

Use VBA Excel Function to sort data in ascending order

In this tutorial, I will show how to use VBA Excel Function to sort data instead of using Filter.

This was originally a question raised in Microsoft Community and was answered by me.

http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/sort-with-a-formula/f15e14e8-ac9f-4ac7-b783-78b98fb38fc0

VBA Code – Custom Excel Function to sort data

Press Alt+F11 > Insert Module > paste the below code

Function wRangeSort(targetRng As Range, dataRng As Range)
    Dim arr()
    For Each Rng In dataRng
        counter = counter + 1
        ReDim Preserve arr(counter)
        arr(counter) = Rng.Value
    Next Rng
    lngMin = LBound(arr)
    lngMax = UBound(arr)
    For i = lngMin To lngMax - 1
      For j = i + 1 To lngMax
        If arr(i) > arr(j) Then
          strTemp = arr(i)
          arr(i) = arr(j)
          arr(j) = strTemp
        End If
      Next j
    Next i
    For k = 1 To lngMax - 1
      For l = k + 1 To lngMax
        If arr(k) = "" Then
          arr(k) = arr(l)
          arr(l) = ""
        End If
      Next l
    Next k
    relativePosition = targetRng.Row - Split(Split(dataRng.Address, ":")(0), "$")(2) + 1
    If arr(relativePosition) = "" Then
        wRangeSort = ""
    Else
        wRangeSort = arr(relativePosition)
    End If
End Function

Algorithm of VBA Code – sort data

– Loop through each Range value in dataRng, assign all Range values to array called arr()

– Apply “Bubble Sort” on arr(), swap smaller value in front and larger value at the back

– Use UCase Function to disregard capital letter, the reason is that capital letter is seen to be larger than small letter by default

– Use Split Function to get the starting row number. For example in dataRng A1:A10, starting row number is 1

– Compare starting row number with targetRng row number, so that we know the relative position in dataRng

– Return arr(relativePosition) value

Function Syntax –  sort data

wRangeSort(targetRng, dataRng)
targetRng This range determines the relative order in the data range. For example, if your data range is A1 to A10, you should put A1 in this parameter in order to return the 1st value after sorting, A5 to return the 5th value
dataRng The whole data range you want to apply sorting, such as A1:A10

How to use

Assume that you have the  following data from A1 to A11. These data contain blank, number, cap letter, small letter, symbol

sort_01

In B1, type the following formula, and then auto fill to A11

=wrangesort(A1,$A$1:$A$11)

Now you will see how column B is sorted in ascending order. The sorting is by blank > symbol > number > alphabet (disregard capital letter or not)

sort_02

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *