Contextures Blog

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:

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

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:

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

_________________

Did you ever get an Excel file from someone else, and try to sort out their Excel VBA code? Or, even worse, open an Excel file that you wrote long ago, and try to remember what all those variables mean?

I spend lots of time staring at Excel code, but apparently I don’t use the right-click menu too often, because I hadn’t noticed a couple of handy commands until recently.

These commands can help you decipher that mysterious code, and unravel the complicated sections.

Excel VBA Quick Info

The first handy command is Quick Info. I have Auto Quick Info turned on in the VBE Editor options, and it helps me remember the syntax as I type the code.

Right-Click for Quick Info

What I didn’t realize was that you can right-click on a variable, function, statement, method, or procedure in the code, and click Quick Info.

A tooltip appears, with details on the selected item.

Excel VBA Definition

The other right-click command that I finally discovered is the Definition command.

Click the Definition command, and it takes you to the selected variable’s definition.

Finding the definition is easy in most procedures, but in a long procedure, with a long list of variables, the Definition command really makes the job easier.

It’s especially helpful if the variable is defined on a different code module!

___________