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.
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])
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