Authors do funny things. Sometimes these things are inadvertent; sometimes they’re the result of trying to “prettify” documents for publication. In either case, editors have to clean up what the authors have done.
One such problem is spaces at the ends of table cells. A table cell should end with the text it contains. If there are spaces after that text, they can cause alignment (and other) problems if they’re allowed to persist into typesetting.
It should be a simple matter to clean up the extraneous spaces: Search for a space followed by an end-of-cell marker and replace with just an end-of-cell marker. But what magic code can we use to find or replace an end-of-cell marker? As it turns out, there isn’t one. But we can still get rid of those spaces with a macro. Here it is, with comments about what’s going on (text following a single quotation mark is a “comment”, which is also in green for clarity):
The Macro
Sub CleanCellEndSpaces()
'Define variables (that is, containers for information) Dim aTable As Table Dim aCell As Cell Dim aRow As Row Dim aColumn As Column Dim aRange As Range 'A specific area of the document Dim aLen As Integer 'A number Dim LastChar As String 'A string of characters (text)
Dim Tracking As Boolean 'True or False Tracking = ActiveDocument.TrackRevisions 'Get setting of revision tracking ActiveDocument.TrackRevisions = False 'Turn off revision tracking
On Error Resume Next 'In case of tables with "vertically merged" cells 'Cycle through tables, rows, and cells
For Each aTable In ActiveDocument.Tables For Each aRow In aTable.Rows For Each aCell In aRow.Cells
CheckAgain:
Set aRange = aCell.Range 'Set aRange variable to the contents of the current cell aRange.End = aRange.End – 1 'Don’t include the end-of-cell marker aLen = Len(aRange.Text) 'Get the length of the cell’s text aString = aRange.Text 'Assign the text to a variable LastChar = Right(aString, 1) 'Get the last character of the text If LastChar = " " Then 'If the last character is a space
aRange.Text = Left(aRange.Text, aLen – 1) 'Set the text to be itself minus the trailing space GoTo CheckAgain 'Go back and check for another space (there may be several)
End If Next aCell Next aRow Next aTable
ActiveDocument.TrackRevisions = Tracking 'Set revision tracking back to its original state
End Sub
The Explanation
Here’s how the macro works.
We start by “dimensioning” (defining) our variables, like this:
Dim aTable As Table
“aTable” is an arbitrary name; I just made it up. But that whole line tells Word that aTable will represent a table in our document. The other “Dim” statements follow suit, with “aCell” representing a table cell, “aRow” representing a table row, and so on.
These three lines deserve special attention:
Dim Tracking As Boolean Tracking = ActiveDocument.TrackRevisions ActiveDocument.TrackRevisions = False
Dimensioning the “Tracking” variable as Boolean tells Word that the variable will be either true or false; those are the only two values it can hold.
Next, we set “Tracking” to the value of ActiveDocument.TrackRevisions. If revision tracking is on, “Tracking” will be set to “True.” If revision tracking is off, “Tracking” will be set to “False.” We do that to remember the current setting for revision tracking, because the next line, “ActiveDocument.TrackRevisions = False” actually turns revision tracking off (we’ll reset it later). This is necessary because (1) tracking the deletion of those extraneous spaces isn’t needed, and (2) using revision tracking may send this macro into an endless loop as it keeps “finding” the character that it just deleted (but is still there as a tracked revision).
The next line —
On Error Resume Next
— needs to be there in case a table includes “merged” cells, which will cause an error when the macro runs. If that happens, the macro will skip to the next line, which means that tables with “merged” cells will be ignored. There may be a better way to deal with such tables, but I don’t know what it is.
After that line, things get really interesting:
For Each aTable In ActiveDocument.Tables
This tells Word to work on “Each” table in ActiveDocument.Tables. “What’s that?” you ask. Well, obviously “ActiveDocument” is the active document — the document that’s open in Word with our cursor moving around in it. (Other documents may be open but not active.) In the active document, there’s a collection of objects called “Tables” — which are, of course, the tables in the document.
And now, a brief digression: As far as macros are concerned, a Microsoft Word document is “simply” a collection of various objects, such as tables, headers, footers, footnotes, endnotes, paragraphs, words, and much, much more. All of these objects have certain “properties.” For example, a paragraph may have the property of FirstLineIndent set to “True” — in other words, its first line is indented. Objects can also contain other objects. For example, a table always contains at least one row and one column. So, in our macro, we have this:
For Each aRow In aTable.Rows
That tells Word to work on each row in the current table. Similarly, this —
For Each aCell In aRow.Cells
— tells Word to work on each cell in the current row.
Next, we’re going to set the range of text we want to use (that is, aRange) to be the current cell:
Set aRange = aCell.Range
Then we’ll reduce the end of that range by one character, so we don’t include the end-of-cell marker:
aRange.End = aRange.End – 1
And, using the Len command, we’ll find out the length (number of characters) included in the range’s text:
aLen = Len(aRange.Text)
Now let’s get that text by putting it into the variable called “aString”:
aString = aRange.Text
And we’ll use the Right command to find out the last character of the text string (that is, the first character on the right of the string):
LastChar = Right(aString, 1)
That looks a little complicated, but it’s actually fairly simple. Let’s say our text string is “Hi, Mom!” The “1” tells the Right command at which character to start counting (from the right of the string). In other words, LastChar is assigned the last character of the string, which in this case is an exclamation mark (“Hi, Mom!”).
But what if the last character is a space? That’s what we really we want to know. The next line will tell us if that’s the case:
If LastChar = " " Then
If the last character is a space, we need to get rid of it, which we can do like this:
aRange.Text = Left(aRange.Text, aLen – 1)
That line changes the text of our range to itself minus its last character (if the previous line identified its last character as a space). But what if there’s more than one space? We want to get rid of those spaces too! And that’s where the next line comes in:
GoTo CheckAgain
That sends the macro back to the “label” we’ve created at this line:
CheckAgain:
And the operation is repeated on the cell until no more spaces remain at the end of the cell.
All of the “Next” commands that follow repeat the whole operation for every cell in every row in every table of the active document. Powerful stuff!
Finally, the macro restores revision tracking to its original setting as stored in the “Tracking” variable:
ActiveDocument.TrackRevisions = Tracking
As they taught us in kindergarten, it’s good to clean up after yourself.
This article is a brief introduction to manipulating Word “objects” with macros. Future articles may explore more of those objects, along with their “properties” and “methods.” If that’s more than you want to know, you may still find the macros themselves to be useful.
How to Add Macro to Word & to the QAT
Here’s how to put this macro (or any other) into Microsoft Word so it will be available when you need it:
- Copy the text of the macro, starting with the first “Sub” and ending with the last “Sub.”
- Click the “View” tab on Microsoft Word’s ribbon.
- Click the “Macros” button.
- Type a name for the macro in the “Macro name” box — probably the name used after the first “Sub.” For this macro, that’s “CleanCellEndSpaces.”
- Click the “Create” button.
- Delete the “Sub [macro name]” and “End Sub” lines that Word created in the macro window. The macro window should now be completely empty (unless you already have other macros in there).
- Paste the macro text at the current insertion point.
- Click “File,” then “Close and Return to Microsoft Word.”
To actually use the macro:
- Place your cursor at the beginning of the document.
- Click the “View” tab on Microsoft Word’s ribbon.
- Click the “Macros” button.
- Click the name of your macro to select it.
- Click the “Run” button. (If you wanted to delete the macro, you could press the “Delete” button instead.)
Here’s how to put the macro on Word’s QAT (Quick Access Toolbar):
- Locate the QAT (it’s probably on the top left of your screen either above or below Word’s Ribbon interface).
- Right-click the QAT.
- Click “Customize Quick Access Toolbar.”
- Under “Choose commands from:” click the dropdown list and select “Macros.”
- Find and select your macro in the list on the left.
- Click the “Add” button to add it to the QAT.
- Click the “OK” button to finish.
Jack Lyon (editor@editorium.com) owns and operates the Editorium, which provides macros and information to help editors and publishers do mundane tasks quickly and efficiently. He is the author of Microsoft Word for Publishing Professionals and of Macro Cookbook for Microsoft Word. Both books will help you learn more about macros and how to use them.
Programs from the Editorium
Have you checked out the Editorium's latest Microsoft Word add-ins to help with your work?
IndexLinker creates hyperlinks from index page numbers back to the text to which they refer. If you're creating ebooks or PDFs with indexes, you need this program.
BookMaker automates typesetting and page layout in Microsoft Word. Stop fighting with page breaks, headers, and footers. Let BookMaker do the heavy lifting.
LyXConverter converts Word documents into LyX documents.
And, of course, many other useful add-ins are available as well, including Editor's ToolKit Plus.
A Special Deal: Editor's Toolkit Ultimate!
Editor's ToolKit Ultimate combines three great products:
- Editor's ToolKit Plus from The Editorium.
- PerfectIt Pro from Intelligent Editing.
- EditTools from WordnSync.
The three products work together to create a powerful editing package to take you through three separate stages of copyediting.
Communication Conference
Wondering how to launch or improve an editorial business, whether you offer writing, editing, proofreading, indexing or other related services? Come to “Be a Better Freelancer! (Re)Invent Your Business,” the ninth annual Communication Central conference for freelancers, September 26–27, 2014, in Rochester, NY, with an Editorial Bootcamp on September 28 at the same location.
Topics include launching your business, macros and other efficiency/productivity tools, working with MS Office, organization tips, a self-publishing roundtable, balancing freelancing and family life, resources, benefiting from social media, and more. Keynote speaker is Jake “Dr. Freelance” Poinier. Other speakers include Erin Brenner, Ally Machate, April Michelle Davis, Daniel Heuman, Katharine O’Moore-Klopf, Dick Margulis, Greg Ioannou, Geoff Hart, Jack Lyon, Laura Poole, Ben Davis, Amy Schneider, and Ruth E. Thaler-Carter.
Interested in Laura Poole’s editorial bootcamp? Info for the Editorial Bootcamp is included in the conference registration PDF. The Editorial Bootcamp may be taken without attending the conference.
You'll find more information here:
http://www.communication-central.com/
Questions? Contact Communication Central owner Ruth E. Thaler-Carter, at conference@communication-central.com or 585-248-0318.