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
'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 comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>

  1. FI

    Hi, is there any way to set the header row on 3rd row or below? Because I wanted to use top row as title for the worksheet.

  2. PL
    powerfull love spell

    What's up,I check your blog named "Sort Column Data By Double Clicking Header Cell Use Excel VBA - Excel Macros" regularly.Your writing style is awesome, keep doing what you're doing! And you can look our website about powerfull love spell.