Tom’s Tutorials for Excel: Auto-Sort By Double-Clicking a Cell

Tom’s Tutorials for Excel: Auto-Sort By Double-Clicking a Cell

This example toggles a sort in ascending and descending order for a table of data whose headers are in row 1. When you double-click any cell in the data range, the table is sorted based on the column of the cell that is double-clicked.

To make this happen, right-click your worksheet tab and then left-click to select View Code. Paste the below code into the large white area that is your worksheet module. Press Alt+Q to return to your worksheet.

Public blnToggle As Boolean

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Dim LastColumn As Long, keyColumn As Long, LastRow As Long
Dim SortRange As Range
LastColumn = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
keyColumn = Target.Column

If keyColumn > LastColumn Then Exit Sub

Application.ScreenUpdating = False
Cancel = True
LastRow = Cells(Rows.Count, keyColumn).End(xlUp).Row
Set SortRange = Target.CurrentRegion
blnToggle = Not blnToggle
If blnToggle = True Then
SortRange.Sort _
Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlYes
Else
SortRange.Sort _
Key1:=Cells(2, keyColumn), Order1:=xlDescending, Header:=xlYes
End If
Set SortRange = Nothing
Application.ScreenUpdating = True

End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
19 comments on “Tom’s Tutorials for Excel: Auto-Sort By Double-Clicking a Cell
  1. Michael Clifton says:

    Hello Tom. This works great. How would I change this code to only sort below a specified row other than 1 – Say I wanted to only sort below row 8? I’m a rookie here but it seem a matter of just changing very little. Thanks.

    • Tom Urtis says:

      Hello Michael – –

      You are correct, very little needs to change.
      Assuming you have a header row and that header row is row 8, which means that from row 9 to however many rows your data resides in, this modification should do what you want:

      Change this

      SortRange.Sort _
      Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlYes
      Else
      SortRange.Sort _
      Key1:=Cells(2, keyColumn), Order1:=xlDescending, Header:=xlYes

      to this (notice I only changed the row start number from 2 to 9):

      SortRange.Sort _
      Key1:=Cells(9, keyColumn), Order1:=xlAscending, Header:=xlYes
      Else
      SortRange.Sort _
      Key1:=Cells(9, keyColumn), Order1:=xlDescending, Header:=xlYes

      And one more thing, insert this line immediately below the procedure name near the top of the procedure so that if you double click on rows 1:7 then no sort will be attempted by VBA.

      If Target.row < 8 then Exit Sub

      • Michael Clifton says:

        WOW – That’s incredible. Thanks for the speedy reply. That works perfectly. I am fairly new to VBA but Excel has been one of my enjoyable passions for years now. I can see I may need to get a book and maybe take a class because I love this stuff.

        One more tweak question.
        How can this same code be changed so that “columns” to the right of say…”G” are left unsorted? Does that have something to do with the statement “Lastcolumn=”.

        • Tom Urtis says:

          You really should have an empty column between column G and any other columns on the worksheet with data in them that you don’t want to sort. In other words, in your case, make sure column H is totally empty.

          You can change this portion of the original code…

          LastColumn = _
          Cells.Find(What:=”*”, After:=Range(“A1”), _
          SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

          to this:

          LastColumn = 7

          Regarding your intention to get a book, I’ve written a few books on Excel and VBA, and my newest one is here, with an accompanying set of 44 videos plus several dozen downloadable workbooks with code examples.

          http://www.amazon.com/Excel-VBA-24-Hour-Trainer-Urtis/dp/1118991370/ref=asap_bc?ie=UTF8

          • Eric M says:

            Hi Tom,

            Thanks for the code, it is most appreciated – i was looking for something like this for a while now as I have a sheet which has protected cells in a range which users will need to sort.

            Similar to Michael my data doesn’t start in Row 1. My data set starts in cell B10 (Header is on Row 9) so I’ve made the following changes to your code as per your earlier reply. However when I double-click it sorts the header as well.

            Note: i do have AutoFilter on Row 9 – could this be causing this?

            Also, is it possible to amend the code so that only double clicking the header Row 9) will trigger the sort?

            Thanks in Advance,
            Eric

            Public blnToggle As Boolean

            Private Sub Worksheet_BeforeDoubleClick _
            (ByVal Target As Range, Cancel As Boolean)

            Dim LastColumn As Long, keyColumn As Long, LastRow As Long
            Dim SortRange As Range
            LastColumn = _
            Cells.Find(What:=”*”, After:=Range(“A1”), _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            keyColumn = Target.Column

            If keyColumn > LastColumn Then Exit Sub
            If Target.Row < 9 Then Exit Sub

            Application.ScreenUpdating = False
            Cancel = True
            LastRow = Cells(Rows.Count, keyColumn).End(xlUp).Row
            Set SortRange = Target.CurrentRegion
            blnToggle = Not blnToggle
            If blnToggle = True Then
            SortRange.Sort _
            Key1:=Cells(10, keyColumn), Order1:=xlAscending, Header:=xlYes
            Else
            SortRange.Sort _
            Key1:=Cells(10, keyColumn), Order1:=xlDescending, Header:=xlYes
            End If
            Set SortRange = Nothing
            Application.ScreenUpdating = True

            End Sub

            • Tom Urtis says:

              Hi Eric – –

              When I tested your code, even with row 9 on AutoFilter, the range sorted without the header, so I do not understand this youwrote:
              “However when I double-click it sorts the header as well.”

              Regarding your question about how to only have row 9 being the trigger for the double-click event, you can insert this line at the very start of the procedure:
              If Target.Row <> 9 Then Exit Sub

          • Eric M says:

            Hi Tom,

            Thanks for the speedy response.

            “However when I double-click it sorts the header as well.” I meant the headers are being moved into the data set, and row 9 is replaced by data from the table below.

            I think I have identified the problem – both Rows 8 & 9 contained headers, i have cleared contents from Row 8 which seems to have fixed it.

            Many Thanks,
            Eric

          • Eric M says:

            Hi Tom,

            Is there any way build into the code the ability to ‘undo’ a sort?

            Thanks
            Eric

            • Tom Urtis says:

              Yes, but with the code you have, the arrangement of data was established with the previous double-click which means all you’d need to do is double-click the same header that you did before the macro was run that you want to undo. Otherwise, you’d need to start the procedure with a few code lines that copy the data before the sort, and paste it into a veryhidden sheet or in a hidden workbook. Then if you want to undo, call the macro you’d create to copy that original range from where you saved it and paste it into the worksheet you are working with.

  2. Eric says:

    I’m new to VBA and am slowly getting to the point where I an use it easily and enjoy it. Is there a way to sort everything in the same way, but rather than sorting when you click anywhere in the column, it only sorts if you double click the header itself?

    Thanks for any help

  3. Brian L Robertson says:

    Dear Tom

    Thanks for your – Public blnToggle as Boolean Sort macro, which solved my bank statement sort. I am 73 and started with Multiplan on a Commodore 64 over 30 years ago. Your Tutorials are an invaluable resource. I have been a GRC (Steve Gibson) fan since 2005 and visited Leo Laporte at TWIT (have a photo)in Petaluma in 2011 as part of a 35 day tour of your wonderful country. Stayed 3 days in San Francisco. I compiled a 231 Row keyboard shortcuts sheet (blnToggle) useful for training that has 4 Cols – Action, Shortcut, Keyword and Example. If you wish, I will email it to you if you provide a suitable email address. I retired in 2010 and was a qualified AASA Accountant. In 1971 I learned RPG2 (for a System 3 Model 6) at a 6 weeks full time IBM course. Thanks again Tom.

  4. William Redden says:

    Tom,

    Could you please provide me with a code to do the following:

    Cell A1 “Item” cell B1 “Qty”

    I need to be able to sort the “Item” column by content, more specifically, color in the description.

    Thank you

    • Tom Urtis says:

      Hello William – –

      Can you clarify please, first, if you want to sort the Item column, and second, is “content” an actual value in some cells in column A, or is it that values in column A are “content” items such as bicycles, basketballs, and beanbags.

      Also, regarding color in the description, do you mean that you want to sort a list by the color of its cells, or do the cells uncolored and you want to color the cells holding a certain value. And, is the color cause by conditional formatting or by actually filling the interior color property of a cell per the cell’s format? Finally, is the cell itself colored or is the color that you referred to the font color of whatever values are in the cells?

      Tasks like this are usually do-able, but it’s hard to tell exactly what you are asking based on your message.

      Tom

  5. Nuno Mira says:

    This is exatcly what I was looking after.
    Thank you very much.

    Is there any way to make the filter modify other designated tables? This way we can keep then all organized by clicking the header on any worksheet.

    • Tom Urtis says:

      Are the tables to be sorted all on the same worksheet, or does each table reside in its own worksheet. If it’s the latter, maybe all you need is a workbook level double click event. If it’s the former, the code in this example can be slightly modified.

  6. Josh says:

    Hi Tom,

    I have a table that has, in A2:A25, character’s ages, and then, in B2:B25, character’s names. How would I modify your code so that I can double click cell A1 for the characters to be sorted by age, oldest at the top and youngest at the bottom?

    Hope you can help!

    Thank you

    • Tom Urtis says:

      Yes that can be accomplished, but how about expanding a bit on your question, just for fun.

      I assume you have header labels in cells A1 and B1.

      In the module of the worksheet where you have the code I posted in this example, delete that entire Worksheet Change event code and replace it with this, which will toggle-sort the range in ascending or descending order for the column (A or B) that you double-click on. That way, you can double click anywhere in the range of data from A1:B whatever and the range will sort ascending or descending based on the column you double-clicked on. This way you have the convenience of not aiming for only cell A1 or B1.

      First, in the worksheet module, make sure both these statements are at the very top of the module, above and outside any other code:
      Option Explicit
      Public blnToggle As Boolean

      Then, copy and paste this into that worksheet module:
      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      If Target.Column > 2 Then Exit Sub
      Dim keyColumn As Long, SortRange As Range
      Cancel = True
      Application.ScreenUpdating = False
      Set SortRange = Range("A1").CurrentRegion
      blnToggle = Not blnToggle
      keyColumn = Target.Column
      If blnToggle = True Then
      SortRange.Sort key1:=Cells(2, keyColumn), order1:=xlAscending, Header:=xlYes
      Else
      SortRange.Sort key1:=Cells(2, keyColumn), order1:=xlDescending, Header:=xlYes
      End If
      Set SortRange = Nothing
      Application.ScreenUpdating = True
      End Sub

      Go back to the worksheet and test the code. Should work for you as it did for me just now.

Leave a Reply

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

*