Excel VBA Click Shape to Sort Column

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

 

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

 

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:

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

 

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

_________________