Excel VBA Click Shape to Sort Column

SortClick00 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?

Dave 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.

Two benefits of using Dave’s code:

  1. Reduced wear and tear on clicking fingers
  2. Less risk of table scrambling, because it ensures the entire table is selected before sorting

SortClick01 

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

  1. On the Excel Ribbon, click the Developer tab, then click Macros
  2. Click SetupOneTime, and click Edit

 SortClick02

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.

SortClick03 

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)

SortClick04

If you want to see the rectangle outlines, change the Line.Visible setting to True.

SortClick06

Run the SetupOneTime Macro

After you’ve edited the macros, you can run the setup macro:

  1. Select the sheet where your table is located.
  2. On the Excel Ribbon, click the Developer tab, then click Macros
  3. Click SetupOneTime, and click Run

SortClick07 

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.

SortClick05

Download the Sample Workbook

To see the full code for the SetupOneTime and SortTable macros, and download the sample workbook, visit the Excel VBA: Sort Data With Invisible Rectangles page on the Contextures website.

Watch the Click Headings to Sort Columns Video

To see the steps for editing the code, adding the rectangles, and clicking the hidden shapes, please watch this short Excel tutorial video.

_________________


You may also like...

3 Responses

  1. Jeff says:

    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

    • Ridge says:

      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

  2. Erik says:

    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

Leave a Reply

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