Saving Sort Criteria in Word

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

How to save a special sort order for your Word tables.

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 no, 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:

1373 Word   Sort table example To Do List - Saving Sort Criteria in Word

And here’s the custom Sort he prefers:

1373 Word   Sort table options for To Do List - Saving Sort Criteria in Word

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/Word 2010 you’ll need to 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’l 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() .

 

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.

subs profile e1563205311409 - Saving Sort Criteria in Word
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address