How to save a special sort order for a Microsoft Word tables and re-use it when the list changes or setup different sort orders that you can apply with the click of a button.
Brian D writes with a question about custom sorting a table, in his case a To Do List. Each time he opens the document he has to re-enter the sort criteria for the Word table and asks if there’s a way to save the sort settings between sessions.
The short answer is not directly, because there’s a better way available in Word. A VBA macro will do the custom sort action and much more.
I know many Office users run screaming from the idea of VBA macros and I can’t say I blame them when many of the examples are complex and specific. But the basics are simple, especially because Office has a record macro feature.
Record Macro is an easy way to convert your standard actions into VBA equivalents. Even experts use record macro to discover how to do something in VBA – it’s easier, faster and often more reliable than looking up the documentation.
Here’s an example table from Brian:
And here’s the custom Sort he prefers:
The same sort via a recorded macro looks like this:
Sub SortTableCustom()
Selection.Sort ExcludeHeader:=True, FieldNumber:=”Column 4″, SortFieldType _
:=wdSortFieldDate, SortOrder:=wdSortOrderAscending, FieldNumber2:= _
“Column 3”, SortFieldType2:=wdSortFieldDate, SortOrder2:= _
wdSortOrderAscending, FieldNumber3:=”Column 1″, SortFieldType3:= _
wdSortFieldAlphanumeric, SortOrder3:=wdSortOrderAscending, Separator:= _
wdSortSeparateByCommas, SortColumn:=False, CaseSensitive:=False, _
LanguageID:=wdEnglishUS, SubFieldNumber:=”Paragraphs”, SubFieldNumber2:= _
“Paragraphs”, SubFieldNumber3:=”Paragraphs”
End Sub
You can save that macro in the document and run it whenever you like.
Any macro/function can be assigned to a keyboard shortcut or a toolbar/ribbon/Quick Access Toolbar.
In Word 2007 and later save the document with a .docm extension to save the VBA code with the document.
In Word 2007/Word 2010 the Record Macro option only appears once you have enabled the Developer tab at Office button | Word Options | Popular | “Show Developer Tab in the Ribbon”. For earlier versions of Word you’ll find Record Macro at Tools | Macro.
Embellishments
The above macro does the job but is a little clumsy in practice. You have to select the table before running the macro; it would be better if the code found the right table too. This ensures the code operates only on the target table (there might be more than one table in the document or the user might accidently select part of the table) and doesn’t generate an error.
A solution to this is bookmarking the table – this gives the table a unique name and ensures the entire table is selected.
Select the entire table then choose Insert | Bookmark and give the bookmark a name like ‘ToDoTable’. We suggest bookmarking because it identifies a specific table in the document; standard VBA methods for finding tables work off the order of tables in a document which could change if more tables are added to the document later.
Here’s the revised function. Record Macro adds some extra lines after Selection.GoTo which we’re removed because they aren’t necessary in this case.
Sub FindandSortToDoTable()
Selection.GoTo What:=wdGoToBookmark, Name:=”ToDoTable”
Selection.Sort ExcludeHeader:=True, _
FieldNumber:=”Column 4″, SortFieldType:=wdSortFieldDate, SortOrder:=wdSortOrderAscending, _
FieldNumber2:=”Column 3″, SortFieldType2:=wdSortFieldDate, SortOrder2:=wdSortOrderAscending, _
FieldNumber3:=”Column 1″, SortFieldType3:=wdSortFieldAlphanumeric, SortOrder3:=wdSortOrderAscending, _
Separator:=wdSortSeparateByCommas, SortColumn:=False, CaseSensitive:=False, _
LanguageID:=wdEnglishUS, SubFieldNumber:=”Paragraphs”, SubFieldNumber2:= _
“Paragraphs”, SubFieldNumber3:=”Paragraphs”
End Sub
We’ve also manually re-arranged the Selection.Sort options so the sort criteria for each column are on a single line. This makes it easier to find and change them later.
The beauty of the VBA approach is that you can make ‘variation on a theme’ functions to sort the list in different ways. All you have to do is copy the original sorting function, change the sort criteria and the function name. For example you could have functions like FindandSortToDoTablebyPriority() and FindandSortToDoTablebyDueDate() .
Trimming a Recorded VBA Macro
Note: when you use Record Macro it sometimes generates extra lines of code that might not be necessary. In this case here’s what we got when recording a macro to Go To the table:
Selection.GoTo What:=wdGoToBookmark, Name:=”ToDoList”
Selection.Find.ClearFormatting
With Selection.Find
.Text = “”
.Replacement.Text = “”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Only the first line is really necessary in this case so we deleted the rest for clarity. It seems that the Record function saves all the Find/Replace/Go To options when you exit that dialog in Record Macro mode. That’s a good general policy but in this simple ‘Go To’ situation the additional lines aren’t required and could confuse people later.
Secret shortcut for fast sorting or reordering in Word
Other Word sorting problems, tricks and workarounds
Limitations of Word Sort feature for name sorting
Sorting in Word
Sorting paragraphs using Word – Part 1
