Excel Macro: Sort Column Data By Double Clicking Header Cell

If you want to sort the column data in ascending or descending order by double clicking on any Excel column header, you will need the following Macro.

Sort Column Data By Double Clicking Header Cell

'------------------ Worksheet ------------------
Public blnToggle As Boolean
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'Step 1: Declare your Variables
    Dim keyColumn As Long, LastRow As Long

'Step 2: Check whether the target cell meets the requirements
    If IsEmpty(Target) Or Target.Row <> 1 Then Exit Sub

'Step 3: set kyeColumn as target column number
    keyColumn = Target.Column

'Step 4: Find last non-empty row
    LastRow = Cells(Rows.count, keyColumn).End(xlUp).Row
    
    blnToggle = Not blnToggle
    If blnToggle = True Then
'Step 5: first double-clicked column will be sorted in ascending order
        Columns(keyColumn).Sort Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlYes
    Else
'Step 6: second double-clicked column will be sorted in descending order
        Columns(keyColumn).Sort Key1:=Cells(2, keyColumn), Order1:=xlDescending, Header:=xlYes
    End If

End Sub

How This Macro Works

1. Step 1: declare two long Variables.
2. Step 2: we use IsEmpty function to check if the target cell (the cell that has double clicked) is an empty, and use Row property of Range object to check if the target row number is not equal to 1. If any condition is true, forces the macro to stop and exit the procedure.
3. Step 3: set kyeColumn as target column number
4. Step 4: Find last non-empty row number of target column
5. Step 5: we use Sort method to sort our data, first double-clicked column header cell will be sorted in ascending order
6. Step 6: second double-clicked column header cell will be sorted in descending order

Download

Download the macro: Sort Column Data By Double Clicking Header Cell

How to Use This Macro