Sort an Excel Range on Double-Click
When you distribute your Excel reports to your customers, it’s often nice to add a few bells and whistles. One of the easier enhancements to apply is the ability to sort when a column header is double-clicked. Although this may sound complicated, it’s relatively easy with this macro.
You can download a working example of this double-click technique in Excel.
How the macro works
In this macro, you first find the last nonempty row. You then use that row number to define the target range of rows that you need to sort. Using the Sort method, you sort the target rows by the column you doubled-clicked.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Step 1: Declare your Variables Dim LastRow As Long 'Step 2: Find last non-empty row LastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Step 3: Sort ascending on double-clicked column Rows("6:" & LastRow).Sort _ Key1:=Cells(6, ActiveCell.Column), _ Order1:=xlAscending End Sub
In Step 1, you declare a Long Integer variable called LastRow to hold the row number of the last nonempty row.
In Step 2, you capture the last nonempty row by starting at the very last row in the worksheet and using the End property to jump up to the first nonempty cell (equivalent of going to cell A1048576 and pressing Ctrl+Shift+up arrow).
Note that you need to change the column number in this cell to one that is appropriate for your data set. For example, if your table starts on Column J, you need to change the statement in step 2 to Cells(Rows.Count, 10).End(xlUp).Row because column J is the 10th column in the worksheet.
In Step 3, you define the total row range for your data. Keep in mind that the range of rows has to start with the first row of data (excluding headers) and ends with the last nonempty row. In this case, your data set starts on row 6, so you use the Sort method on Rows(“6:” & LastRow).
The Key argument tells Excel which range to sort on. Again, you will want to ensure that the range you use starts with the first row of data (excluding the headers).
How to use the macro
To implement this macro, you need to copy and paste it in the Worksheet_BeforeDoubleClick event code window. Placing the macro here enables it to run each time you double-click the sheet:
Activate Visual Basic Editor by pressing ALT+F11.
In the Project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
Click the sheet from which you want to trigger the code.
In the Event drop-down box, select the BeforeDoubleClick event.
Type or paste the code.