Macro 42: Sort a 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.
How it works
In this macro, we first find the last non-empty row (using the concepts outlined in this chapter under
“Macro 40: Find and Select the First Blank Row or Column”). We then use that row number to define
the target range of rows we need to sort. Using the Sort method, we sort the target rows by the col-
umn we 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
1. We first declare a Long Integer variable called LastRow to hold the row number of the
last non-empty row.
2. In Step 2, we capture the last non-empty row by starting at the very last row in the worksheet and
using the End property to jump up to the first non-empty cell (equivalent of going to cell
A1048576 and pressing Ctrl+Shift+Up Arrow on the keyboard).
Note that you need to change the column number in this cell to one that is appropriate for
your data set. That is to say, if your table starts on Column J, you would need to change the
statement in Step 2 to Cells(Rows.Count, 10).End(xlUp).Row because column J
is the tenth column in the worksheet.
3. In this step, we define the total row range for our data. Keep in mind that the range of
rows has to start with the first row of data (excluding headers) and end with the last non-
empty row. In this case, our data set starts on row 6. So we use the Sort method on
Rows(“6:” & LastRow). The Key argument here tells Excel which range to sort on.
Again, you will want to ensure the range you use here starts with the first row of data
(excluding the headers).
How to use it
To implement this macro, you need to copy and paste it into the Worksheet_BeforeDouble
Click event code window. Placing the macro there allows it to run each time you double-click on
the sheet.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click on the sheet from which you want to trigger the code.
4. Select the BeforeDoubleClick event from the Event drop-down list (see Figure 4-6).
5. Type or paste the code.
Figure 4-6: Type or paste your code in the Worksheet BeforeDoubleClick event code window.
