Create and save custom sort orders for Word, you easily apply them when list changes. You can also set up different sort orders that can be applied with a single click, streamlining your workflow and enhancing efficiency.
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.
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:
Record Macro option only appears on the bottom Status Bar once you have enabled the Developer tab.
Start recording a macro then go to Table Sort, setup the sort you want and click OK. Stop macro recording.
VBA code to sort a table
The same sort as above 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.
Simple edits can be made to the macro directly and it’s common to do that for recorded macros which often have unnecessary extras.
Any macro/function can be assigned to a keyboard shortcut or a toolbar/ribbon/Quick Access Toolbar.
Save the document with a .docm extension to save the VBA code with the document.
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.
Bookmark the table
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.
