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: , , , , , , , , , , , ,
35 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.

  7. Kevin McFarlane says:

    Tom, help me lolol!!!

    I don’t know much about Excel, and VBA puzzles my mind.

    I have a spreadsheet table with headers ranging from Column A to column L.

    I have 4 specific colored coded headers I want to double click on to sort the entire row from A to L.

    So let’s say i double click on header F2(green), any rows with the color green will sort to the top

    G2(red) etc…

    H2(yellow) etc…

    And L2(blue) etc…

    (As time goes on, the rows will increase so I guess i got to take that into consideration)

    And to reset to it’s original layout

    Double click on header A2…is this possible??

    • Tom Urtis says:

      Clarifying a few points…

      Your headers are in row 2, so what is in row 1.

      You wrote:
      ” to sort the entire row from A to L”
      Really? The entire row but not the column?

      Are these cells actually colored or do they contain the word green or red or whatever.

      What is the color hierarchy to know what color goes ahead of what color.

      Are the colors from conditional formatting or did you change the actual interior cell colors.

      I am jammed up for the next 2 weeks with client projects and can’t get to this, especially with all these unknowns. I suggest you post your question on the mrexcel.com forum where Excel experts monitor the board around the clock. It’s free and easy, and I contribute to it too when I have the time.

  8. Jeff Brown says:

    Hello Tom,
    Thank you for providing us with this code. I am using Office 365 in Windows 10.

    I changed:
    Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlYes
    to
    Key1:=Cells(4, keyColumn), Order1:=xlAscending, Header:=xlYes
    and added
    If Target.Row 4 Then Exit Sub
    to be the first command after the Sub name.

    I am getting a Run-time error ‘1004’: Sort method of Range class failed. When I click debug, these rows are highlighted:
    SortRange.Sort _
    Key1:=Cells(4, keyColumn), Order1:=xlAscending, Header:=xlYes

    I should mention that I am trying to use this for sorting a table in case that matters.

    • Tom Urtis says:

      It may be (and usually is) due to HTML being HTML but your codeline looks like this as I read it:
      “If Target.Row 4 Then Exit Sub”

      Maybe you meant If Target.Row = 4 Then Exit Sub
      which is what you should have.

      If row 4 is your header row then you should have this:
      If Target.Row < = 4 Then Exit Sub Key1:=Cells(5, keyColumn), Order1:=xlAscending, Header:=xlYes

      • Jeff says:

        Hi Tom,

        Yes, I meant ‘If Target.Row <= 4 Then Exit Sub'

        I changed both Key1 lines to:
        Key1:=Cells(5, keyColumn), Order1:=xlAscending, Header:=xlYes
        but I am still getting the same error error at that line.

        I thought maybe it was because the table was already filtered so I cleared all the filters and made sure every column was visible but it still throws the same error.

        Here is the code:

        Public blnToggle As Boolean

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

        If Target.Row 4 Then Exit Sub

        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(5, keyColumn), Order1:=xlAscending, Header:=xlYes
        Else
        SortRange.Sort _
        Key1:=Cells(5, keyColumn), Order1:=xlDescending, Header:=xlYes
        End If
        Set SortRange = Nothing
        Application.ScreenUpdating = True

        End Sub

        • Tom Urtis says:

          • What the range of cells is that this data occupies.
          • What is the range of header cells.
          • What is the range of data cells.
          • Are any (any means any) cells merged.
          • Is there a specific column you only want to sort by, or do you want to double click anywhere in the range and have the data sort by that column where the double click happened.

          Look especially for merged cells.

  9. Sandeep G Nath says:

    Hi Tom,
    Can I manually sort cells or copy paste cells to a new column by double cliking that single cell. The new column should be in the double clicking order.

    To make clear say if data on column 1 is sl no.- 1 to 10. Double click on 8 copy 8 on top of column 2. Double click on 2 copy 2 just below 8 on column 2. and so on.

    Hope you could help me.

    • Tom Urtis says:

      I’m sure it’s possible but I am not totally clear on what you mean. The double-click event would be used so that is no problem, but what for example does “Double click on 8 copy 8 on top of column 2” mean? On the same row? Or put 8 in all 10 cells in column 2? Try giving a more detailed example.

  10. Evan H. says:

    Thank you very much for posting this code. Exactly what I was looking for, with one little hiccup.

    I have a dual row header, row 1 for “categories”, row 2 for the individual parameters. So for example, A1:J1 are merged and contain category name; A2-J2 are typical unmerged headers in that category. K1:Q1 merged with next category name; K2-Q2 typical unmerged headers, etc.

    I would like to sort starting from row 3, only when dbl clicking the header in row 2. My code attempt below.

    I am finding that if I delete all the of the text in row 1, everything works as I expect. But if any of the cells on row 1 contain any text, it sorts the row 2 header along with all the other rows. The behavior appears blind to whether cells in row 1 are merged and only cares whether there is any text.

    How may I modify this so it sorts rows 3 onward, even with text in both rows 1 and 2?

    Public blnToggle As Boolean

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

    If Target.Row 2 Then Exit Sub

    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(3, keyColumn), Order1:=xlAscending, Header:=xlYes
    Else
    SortRange.Sort _
    Key1:=Cells(3, keyColumn), Order1:=xlDescending, Header:=xlYes
    End If
    Set SortRange = Nothing
    Application.ScreenUpdating = True

    End Sub

    • Tom Urtis says:

      A couple things about your post is unclear but I get the general idea.
      I wish Microsoft would eliminate the Merge Cells feature.
      Merged cells are the scourge of VBA and humankind. Better to format as Center Across Selection.

      Let’s suppose for a moment you insert an empty row at row 2 and you hide row 2. Now that means if you were to click one of the cells in what is then row 3, that would solve your problem, with a little editing of my original code.

      What is unclear to me is if for example you double click on what is currently cell A2, then should only range A3:J_LastRow be sorted, or should A3:LastColumn_LastRow be sorted. I am always suspicious of what end result is actually expected when merged cells are involved.

  11. Kasie says:

    Good afternoon,

    I added the code above to my worksheet and it worked beautifully! Thank you so much.

    But, then I added a column to my worksheet with the below code that allows me to enter a checkmark by double clicking in column A, and received an error. Is there a way to alter your code above so that I can click anywhere in column C to sort the table, instead of any column? I think that will fix my error.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ‘ Add/Remove a Check Mark from a Cell

    ‘ Made by: https://www.TeachExcel.com

    ‘ Excel Courses: https://www.teachexcel.com/premium-courses/?src=tut_id_2444

    If Not Intersect(Target, Range(“a6:a300”)) Is Nothing Then

    Target.Font.Name = “Marlett”

    If Target.Value = “a” Then

    Target.Value = “”

    Else

    Target.Value = “a”

    End If

    Cancel = True

    End If

    End Sub

    Thank you!

  12. Kasie says:

    Hi, thank you for your help, but I am still receiving this error:

    Compile error:
    Amiguous name detected: Worksheet_BeforeDoubleClick

    I will try to explain a little better. I would like to incorporate both Macros, if possible.

    If I click in column A, I would like to place a checkmark.

    If I click in any other column, I would like to sort by that column.

    (I am changing my original request to only sort for Column C).

    Thank you,

    • Tom Urtis says:

      I tested the code before I posted it so I know it works.

      The ambiguous error probably means you have 2 BeforeDoubleClick event procedures in the same worksheet module. Combine both of them into just one.

Leave a Reply

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

*