Making SORT More Simple and Faster in Excel with Advanced Options

Usual Sorting of Data or Table in Excel

To Sort a Column Data in Excel, the user has to select the data range or column and then choose option “Sort & Filter”. From here, they can choose either “Smallest to Larger or A to Z” or “Largest to Smallest or Z to A” or a “Custom Sort”.

Once User clicks any of these options, again Excel will ask whether user want to “Expand the Selection” or “Continue Sort with Current Selection”.

User has to pass through all these option before performing a simple sort operation.

Create Short Cut & SORT Data Quickly

It will much of boring to perform a well known operation with no short cuts,  when user has to sort lot of data, multiple times while doing any analysis work.

In this article, we are going to see 2 example code snippets.

• One will just SORT the selected Range of data and
• Second example will SORT a table by defining any one column as Key Field.

Also Read: Add Auto Filter Data Using Multiple Criteria with the help of Excel Macro

1. Sorting Selected Range and Continue with Current Selection

VBA code to do this is just so simple one and is given below. We will use the Sort function available in VBA and pass the required parameters.

Sub Sort_Selected_Range()
    
    'Select Data Range in Excel Sheet & then Execute this Code
    Selection.Sort Key1:=Range(Selection.Address) _
            , Order1:=xlAscending, DataOption1:=xlSortNormal

End Sub

Select the Data Range or array that you want to sort and then execute the above code. (To execute this code more quickly, assign to a short cut key from Developer Tab -> Macros -> Options (Excel 2010 & above) ).

This code will sort the selected array of date in ascending order directly without asking to choose any options from list of Pop-up Menus. If you want to sort it in descending order or want to perform more advanced operations, refer the complete syntax of this function.

Syntax for Sort function:

Function Sort ([Key1], [Order1 As XlSortOrder = xlAscending], [Key2], [Type], [Order2 As XlSortOrder = xlAscending], [Key3], [Order3 As XlSortOrder = xlAscending], [Header As XlYesNoGuess = xlNo], [OrderCustom], [MatchCase], [Orientation As XlSortOrientation = xlSortRows], [SortMethod As XlSortMethod = xlPinYin], [DataOption1 As XlSortDataOption = xlSortNormal], [DataOption2 As XlSortDataOption = xlSortNormal], [DataOption3 As XlSortDataOption = xlSortNormal])

Also Read: Create Your Own Excel Formula that can be used directly in Excel Worksheets

2. Sorting Selected Range and Expand the Selection
For this method also we will be using the SORT function. But, first we will add the Sorting keys, and then apply the Expansion to Data in other applicable columns.
Let’s see how to do this with an example code snippet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    'Call Submodule to Sort when Currect Worksheet Selection Changes
    Sort_Column_On_Click Target
     
End Sub

Public Sub Sort_Column_On_Click(ByVal Target As Range)
    
    'Check whether the selection is a Column
    If (VBA.Left(VBA.Trim(Target.Address), 2) = VBA.Right(VBA.Trim(Target.Address), 2)) _
        And Not (VBA.IsNumeric(VBA.Mid(VBA.Trim(Target.Address), 2, 1))) Then
        
        'Clear Sort Field Keys if anything is defined already
        ThisWorkbook.Sheets(ActiveSheet.Name).Sort.SortFields.Clear

        'Define New Sort key as selected Column
        ThisWorkbook.Sheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range(Target.Address) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        'Expand Selection to All Columns and Apply Sort for Above Defined Sort Key
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:Z1048576")  'Change this Range if Data goes beyond this limit
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
     End If

End Sub

Copy the code to Excel VB editor. Now go the sheet in which data is present and click on the Column (Column A, B or C).

You can notice that whichever Column you click will get sorted and expand the selection to other columns as well. Pay attention to each comment provided inside the code, which explains about what the code is performing in each line.

These code snippets can be further modified to your need and optimize the time consuming efforts.

More Tips:  Lock System at Scheduled Time with Excel