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
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.
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
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=”.
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
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
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
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
Thanks for the follow-up!
Hi Tom,
Is there any way build into the code the ability to ‘undo’ a sort?
Thanks
Eric
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.
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
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.
Hello Brian, thank you so much for your comment and kind words. My email address is in the footer of every page on my website, and also on my Contact page.
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
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
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.
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.
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
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 ExplicitPublic 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.
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??
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.
Hey,thanks for getting back too, sorry I was pretty vague. I’m currently getting help now, appreciate the response back!
Thanks for your follow-up, Kevin; much appreciated.
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.
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
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
• 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.
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.
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.
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
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.
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!
You said anywhere in column C so this is what you are asking for, which I edited for simplicity.
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,
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.