Sort Column Data By Double Clicking Header Cell Use Excel VBA

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 VBA code. See the screenshot:
Sort Column Data By Double Clicking Header

VBA code

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: set kyeColumn as target column number
    keyColumn = Target.Column

'Step 3: Find last non-empty row
    LastRow = Cells(Rows.Count, keyColumn).End(xlUp).Row
    
    blnToggle = Not blnToggle
    If blnToggle = True Then
'Step 4: first double-clicked column will be sorted in ascending order
        Columns(keyColumn).Sort Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlYes
    Else
'Step 5: 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

You can download the working example: Sort Column Data By Double Clicking Header.

How to Use This Macro

To use this macro, you can copy and paste it into a standard module:

  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 to see all the sheets.
  3. Click on the sheet from which you want to trigger the code.
    event code window
  4. Type or paste the code.

Leave a Reply

Your email address will not be published. Required fields are marked *