Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Sort Function

                   Today we will discuss about using Sort function through VBA. So assume you have below kind of excel sheet. You can notice that values in column A under the title "NO" are not in and order. So what we going to do is sort the data according to the values in column  A.




Just go to the Data tab of excel application. You will see below icon in that tab.




                   If you click that icon, it will pop-up a table similar to below. From that table you can choose based on what column you going to sort the data. Based on what, you going to sort the data. And also you can select the order as well.




                      After selecting the parameters accordingly, you can click the OK. Button. Then worksheet will end up with result similar to shown in below image.




So now we have come to the important part. What we are going to do is complete the above process through VBA. You can use below code for that. This sample code will sort data from cells A2 to E13 based on values in the 1st column.

Dim WS As Worksheet
Set WS = ActiveSheet

WS.Range("A3:E13").Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    ("A3:A13"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range("A3:E13")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Also you can use this subroutine to sort data in ascending order. This subroutine take data range, sort fields as parameters. So you can readily use this inside your projects as separate sub routine.

Sub SortAscending(WS As Worksheet, FirstColumn As String, LastColumn As String, KeyColumn As String, _
FirstRow As Long, LastRow As Long)

WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

And this is the subroutine for sort descending

Sub SortDescending(WS As Worksheet, FirstColumn As String, LastColumn As String, KeyColumn As String, _
FirstRow As Long, LastRow As Long)

WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Sometimes we need to add two or more levels when we sorting the data. This is how we do it manually.



Following is a subroutine which can use for two levels. This will sort both levels in ascending order.

Sub SortAscendingTwoLevels(WS As Worksheet, FirstColumn As String, LastColumn As String, _
KeyColumn As String, SecondKeyColumn As String, FirstRow As Long, LastRow As Long)

WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
WS.Sort.SortFields.Add Key:=WS.Range _
    (SecondKeyColumn & FirstRow & ":" & SecondKeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub




If you want to sort both levels in descending order, then you can use below subroutine.

Sub SortDescendingTwoLevels(WS As Worksheet, FirstColumn As String, LastColumn As String, _
KeyColumn As String, SecondKeyColumn As String, FirstRow As Long, LastRow As Long)

WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow).Select
WS.Sort.SortFields.Clear
WS.Sort.SortFields.Add Key:=WS.Range _
    (KeyColumn & FirstRow & ":" & KeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
WS.Sort.SortFields.Add Key:=WS.Range _
    (SecondKeyColumn & FirstRow & ":" & SecondKeyColumn & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With WS.Sort
    .SetRange WS.Range(FirstColumn & FirstRow & ":" & LastColumn & LastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Contact Form

Name

Email *

Message *