Skip to main content

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

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code into a Standard Module:

  1. Activate the Visual Basic Editor by pressing ALT + F11.
  2. Right-click the project/workbook name in the Project Window.
  3. Choose Insert -> Module.
  4. Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
  5. Click Run button on the Visual Basic Editor toolbar.
  6. For more information, learn this course: Programming with Excel VBA

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>