# 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.

## 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 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) 