People are lazy! Shocking, I know, but who wants to click twice in Excel, if you can do the same thing by only clicking once?
Click to Sort Column
Peterson, champion of weary Excel users, created this sample Excel VBA sort code, that adds invisible rectangles at the top of each column in a table.
A macro is automatically assigned to each rectangle, and it sorts the table by that column, when you click it.
Benefits of Sort Macros
Here are two benefits of using Dave’s code:
- Reduced wear and tear on clicking fingers
- Less risk of table scrambling, because it ensures the entire table is selected before sorting
Edit the Setup Macro
There are two macros in Dave’s sample file.
- SetupOneTime – run this once, to add the hidden rectangles
- SortTable – sorts table by selected column, when heading is clicked
Before you run the SetupOneTime macro, you should edit both macros, to adjust them for your workbook
- On the Excel Ribbon, click the Developer tab, then click Macros
- Click SetupOneTime, and click Edit
The Setup Macro Code
In the SetupOneTime macro, change the iCol variable to match the number of columns in your table. If your table doesn’t start in cell A1, change that reference.
Edit the SortTable Macro
Next, change the variables in the SortTable macro, to suit your table settings. You can adjust:
- TopRow (row where headings are located)
- iCol (number of columns in the table)
- strCol (column to check for last row)
If you want to see the rectangle outlines, change the Line.Visible setting to True.
Run the SetupOneTime Macro
After you’ve edited the macros, you can run the setup macro:
- Select the sheet where your table is located.
- On the Excel Ribbon, click the Developer tab, then click Macros
- Click SetupOneTime, and click Run
Now, click a heading in the table, to sort by that column.
Excel 2007 Shapes Problem
When I was getting this blog post ready, I discovered that Dave’s original code needed a tweak before it would work correctly in Excel 2007 and Excel 2010.
In the original code, written for Excel 2003, there was one line of code that made the rectangular shape invisible:
.Fill.Visible = False
In the newer versions of Excel, only the borders of the invisible shapes were clickable, so I had to change the code to these two lines:
.Fill.Solid .Fill.Transparency = 1#
The revised code worked for me in Excel 2003, 2007 and 2010, creating transparent shapes that were clickable.
Download the Sample Workbook
To see the full code for the SetupOneTime and SortTable macros, and download the sample workbook, visit the Sort Data With Excel Macros page on the Contextures website.
And for more Excel advanced sorting tips, go to the Excel Add-in Advanced Sorting page on my Contextures site.
Watch the Click Headings to Sort Columns Video
To see the steps for editing the code, adding the rectangles, and clicking the hidden shapes, you can watch this short Excel tutorial video.
_________________
When I try to run the sortTable macro, I get the following error: “Run-time error ‘-2147352571 (80020005)’: The item with the specified name wasn’t found.
It is pertaining to this line of code: myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
Is there something I am doing wrong? Thank you for your help!
Best,
Jeff
Jeff, I’m running into the EXACT same issue and can’t find anywhere on the web to get a good answer. Any advice anybody?! Here’s my code below:
Sub SetupOneTime()
‘adds rectangle at top of each column
‘code written by Dave Peterson 2005-10-22
Dim myRng As Range
Dim myCell As Range
Dim curWks As Worksheet
Dim myRect As Shape
Dim iCol As Integer
Dim iFilter As Integer
iCol = 7 ‘number of columns
‘ 2010-Oct-31 added space for autofilter dropdowns
‘ set iFilter to 0 if not using autofilter
iFilter = 12 ‘width of drop down arrow
Set curWks = ActiveSheet
With curWks
Set myRng = .Range(“B4”).Resize(1, iCol)
For Each myCell In myRng.Cells
With myCell
Set myRect = .Parent.Shapes.AddShape _
(Type:=msoShapeRectangle, _
Top:=.Top, Height:=.Height, _
Width:=.Width – iFilter, Left:=.Left)
End With
With myRect
.OnAction = ThisWorkbook.Name & “!SortTable”
” 2010-Oct-31 revised to fill shapes in Excel 2007
” .Fill.Visible = False
.Fill.Solid
.Fill.Transparency = 1#
.Line.Visible = True
End With
Next myCell
End With
End Sub
Sub SortTable()
‘code written by Dave Peterson 2005-10-22
‘2006-08-06 updated to accommodate hidden rows
Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim FirstRow As Long
Dim TopRow As Long
Dim LastRow As Long
Dim iCol As Integer
Dim strCol As String
Dim rng As Range
Dim rngF As Range
TopRow = 4
iCol = 7 ‘number of columns in the table
strCol = “B” ‘ column to check for last row
Set curWks = ActiveSheet
With curWks
LastRow = .Cells(.Rows.Count, strCol).End(xlUp).Row
If Not .AutoFilterMode Then
Set rng = .Range(.Cells(TopRow, strCol), _
.Cells(LastRow, strCol))
Else
Set rng = .AutoFilter.Range
End If
Set rngF = Nothing
On Error Resume Next
With rng
‘visible cells in first column of range
Set rngF = .Offset(1, 0).Resize(.Rows.Count – 1, 1) _
.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0
If rngF Is Nothing Then
MsgBox “No visible rows. Please try again.”
Exit Sub
Else
FirstRow = rngF(1).Row
End If
myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
Set myTable = .Range(strCol & TopRow & “:” _
& strCol & LastRow).Resize(, iCol)
If .Cells(FirstRow, myColToSort).Value _
< .Cells(LastRow, myColToSort).Value Then
mySortOrder = xlDescending
Else
mySortOrder = xlAscending
End If
myTable.Sort key1:=.Cells(FirstRow, myColToSort), _
order1:=mySortOrder, _
Header:=xlYes
End With
End Sub
Hi all and thanks for your interesting code.
For me it works fine, yet only on columns that are fully filled; i.e. with data in each cell.
How can the code be made working correctly on columns with part of the cells empty?
Thanks in advance for your thoughts.
Erik