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
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.
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
|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
In B1, type the following formula, and then auto fill to A11
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)