Microsoft Excel makes it easy to sort a simple table of data. It is a standard operation that most Excel users will visit often. What, though, if you want to perform the sort as part of a macro?
We know that anything a user can do we can normally do within a macro script, the ability to perform a sort is a given … but how can we make a general purpose sorting macro so we can use it in any Excel spreadsheet?
There is little point reinventing the wheel over and over. After all, macros are there to stop us doing repetitive tasks manually. Let us take a look how we can take a macro and make it generic enough that it becomes reusable, and therefore far more useful!
Recording a Macro
First we can start by recording the sorting macro to see how that works. Excel allows us to “record” us performing an operation then “play back” the steps. What it actually does which is pretty smart is puts the steps together in programming code, so we can use it as the foundation for our macro script.
To start the macro recording, find the “Record Macro” command from the “Macros” sub-menu of the “View” bar. A new macro is automatically given a name such as Macro1. I renamed mine to ‘Sorting’. Go figure.
Next, initiate the sort operation using the “Sort” command from the Data bar, selecting the “Atomic Number” column from the columns combo box in the Sort dialog.
Hit “OK”, and the sorting is done. You can then end the recording using the Stop Recording option from the Macro sub-menu.
As mentioned earlier, behind the scenes you were just generating code. Let’s see what we’ve got here:
As you can see, each macro in Excel is generated as a Sub.
The first three rows in the actual macro clear the old sort (if any), and add new one based on the second column (B). Then the sort options are set, including the range on which to perform the sort, and the existence of a header on the range.
Finally, the sort is applied using the chosen parameters.
Making the Macro More Useful
So far it would be just as easy to run your sort from the standard Excel menu system. Let’s make it more customized.
In order to easily run the macro without having to use the “View Macro” command and then press the “Run” button, I used the “Options” button on the View Macro dialog and set a keyboard shortcut to the Sorting macro – specifically, Ctrl+Shift+S. When the file is open, you can use that keyboard shortcut to run the macro, sorting the table as quick as a flash.
OK, now we are getting somewhere. It’s a great macro, and I can use it any time to sort any sheet, except for a few problems:
- It will only work on a worksheet called “square”.
- It will only work on the exact same number of columns and rows.
- It will only sort according to the B column.
Well, that simply will not do. We are trying to make life easier, rather than force our spreadsheet to match our macro! So we need to generalize the macro to overcome those problems and make it a “one size fits all” solution.
Here is what we need to do:
- Use the current worksheet.
- Get the used range from the sheet so the sorting will affect all columns.
- Use the currently selected cell’s column as the key for the sorting.
Using the Current Worksheet
Excel macros have a shortcut name for the current worksheet called “ActiveSheet”. Wherever it appears in a macro, Excel will use the currently active worksheet. This will make it easy to fix the first issue, seeing as the recorded macro only applies to the specific sheet it was recorded on.
Next, for simplicity sake assuming we would like to sort all the data in the sheet, we need to find out the last column and row in the sheet that has data in it.
To do this, we can use a neat property called “UsedRange”. When using the property in a macro, Excel replaces it with a square block of cells containing all rows and columns from the first used cell to the last.
So, to sort the whole data in the sheet, regardless of the actual size and shape of it, we can set the sort’s range to work on the whole used range:
Finally, we really need to be able to sort the table according to any column rather than hard-code it to perform the sort using the same column each time. To make it easier to code, I decided to use the currently selected cell. To do this, we need a few things:
- Find the selected cell.
- Get the column from the selected cell.
- Find the last used row.
From those pieces of info, we can build the key’s range definition.
Lucky for you I have created two functions that will return the column and the row of a cell I pass to them called, funnily enough, “Get Column” and “Get Row” (you can see them at the end of the article).
Basically we get the address of the cell as column$row (for example, “B$12”), and then keep the part before the $ sign to get the column, and after to get the row.
The selected cell can be accessed by a macro using “ActiveCell”, much the same as the active worksheet can be accessed via “ActiveSheet”. To get the last cell of the used range, I used a range property that returns the last cell in the range; SpecialCells(xlCellTypeLastCell). Using it on the UsedRange of the sheet returns the last used cell, from which I took the row.
Now we have all the necessary data to create the sort key range. We’ll replace the original “B2:B21” with “<column>2:<column><row>”:
sKey = sSortColumn & "2:" & sSortColumn & sLastRow
And then use this to create the key.
ActiveSheet.Sort.SortFields.Add Key:=Range(sKey) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
The full macro now looks like this:
Now, I can sort the table by any column just by selecting any cell in it and pressing Ctrl+Shift+S and my work here is done!
You can get the file with the whole macro (and assisting functions) Here.
Over to You
How would you improve this macro? Do you do anything similar? Go ahead, share your thoughts in the comments right now …
About the author
Yoav Ezer co-authors Codswallop, a technology and productivity blog. He is also the CEO of a software company that produces PDF to XLS conversion software.