Lyonizing Word: Removing Spaces at the End of Table Cells

Removing Spaces at the End of Table Cells

by Jack Lyon

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 ‘That is, a specific area of the document
Dim aLen As Integer ‘That is, a number
Dim LastChar As String ‘That is, 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


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:


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:

  1. Copy the text of the macro, starting with the first “Sub” and ending with the last “Sub.”
  2. Click the “View” tab on Microsoft Word’s ribbon.
  3. Click the “Macros” button.
  4. 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.”
  5. Click the “Create” button.
  6. 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).
  7. Paste the macro text at the current insertion point.
  8. Click “File,” then “Close and Return to Microsoft Word.”

To actually use the macro:

  1. Place your cursor at the beginning of the document.
  2. Click the “View” tab on Microsoft Word’s ribbon.
  3. Click the “Macros” button.
  4. Click the name of your macro to select it.
  5. 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):

  1. Locate the QAT (it’s probably on the top left of your screen either above or below Word’s Ribbon interface).
  2. Right-click the QAT.
  3. Click “Customize Quick Access Toolbar.”
  4. Under “Choose commands from:” click the dropdown list and select “Macros.”
  5. Find and select your macro in the list on the left.
  6. Click the “Add” button to add it to the QAT.
  7. Click the “OK” button to finish.

Jack Lyon ( 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.

This entry was posted in Computers and Software, Lyonizing Word and tagged , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.


  1. Posted May 5, 2014 at 5:15 pm | Permalink

    Thanks! This would have come in really handy last month when I was working on a report with tons of tables, with lots of extraneous spaces, especially at the end of cells. I know this will come in handy in the future, because I’m always getting tables with wacky formatted to edit.

  2. Posted May 7, 2014 at 1:50 am | Permalink

    This macro is just brilliant! Thanks so much for solving an issue that bugs me.

    Now a question… My client uses borderless tables in the headers and footers and sometimes excess spaces end up in there too. How do I modify the range to include tables in the headers and footers too?

    Many thanks again.


    • Posted May 15, 2014 at 3:33 pm | Permalink

      Hi, Rhonda.

      Actually, the macro should take care of all tables in the document, no matter where they are. If that’s not occurring in one of your documents, I’d appreciate it if you could send me the document for a quick diagnosis.


      Best wishes,
      Jack Lyon
      The Editorium

  3. Huw
    Posted July 8, 2014 at 3:03 am | Permalink

    Thanks. I got the macro working after having to edit single and double quotes, and minus signs. However it is changing cell text styles. The document I am working with has multiple direct (aka hard) formats applied to words within each paragraph (for which Word [2003], as always, created its own styles). After running the macro, every cell that ended with a space has all its text changed to one style, although it isn’t necessarily the style of the first or last character in the cell before running the macro. Any ideas?

    • Posted August 9, 2014 at 12:22 pm | Permalink

      Sorry, but I don’t know why the style would change. I don’t think there’s anything in the macro that would do that. If you’ll send me the document in which this is happening, I’ll be happy to take a look.

  4. Paul
    Posted August 8, 2014 at 9:02 pm | Permalink

    Great idea but cannot get it to work. Have 1 or 2 spaces after text and blank line below text in Word table. Thank you.

    • Posted August 9, 2014 at 12:29 pm | Permalink

      If the macro isn’t working for you, it may be because of formatting changes applied by WordPress. For example, slanted single quotation marks in the macro should be straight quotation marks. For example, this line–

      Dim aLen As Integer ‘That is, a number

      –should look like this:

      Dim aLen As Integer ‘That is, a number

      That’s difficult to see online, but it makes all the difference in the world.

      You may also encounter problems with “soft” returns after each line of the macro; they should be hard returns instead, and you may have to change them by hand.

      Sorry about the problems, but I don’t yet know a way of preventing them in WordPress. Thanks!

  5. Ewald Fock
    Posted October 25, 2014 at 6:23 pm | Permalink

    Thank you for writing this macro and explaining every line, you rock!!

    I get errors in the following lines (Word 2013)

    aRange.End = aRange.End – 1
    “compile error: expected end of statement” the “-” sign is highlighted.

    aRange.Text = Left(aRange.Text, aLen – 1)
    “Compile error: Espected:list seperator or)” the “-” sign is highlighted.

  6. Posted October 28, 2014 at 7:52 pm | Permalink

    Hi, Ewald. Please see my answer to question number 5, above.

    • Ewald Fock
      Posted October 29, 2014 at 4:52 pm | Permalink

      Thank you Jack, not sure why. but just retyping the”-” sign cleared the error.
      Maybe the – sign copied as an incorrect ASCI code?

  7. juliacochrane
    Posted November 5, 2015 at 10:57 am | Permalink

    What if I want to get rid of extra paragraph returns at the end of table cell?

  8. Rich Marshall
    Posted May 4, 2016 at 1:55 pm | Permalink

    This macro has accomplished something I have been attempting to solve for years. I tried it on a Word 2010 table I had just imported from Excel and it cleaned out all of the spaces before the cell end mark faster than I could see it. I have been able to do this outside tables with the replace command using ^p for the find and ^p for Replace, but the table space has forever stumped me. Thank you for your work.

  9. Posted May 8, 2016 at 5:25 pm | Permalink

    Fantastic! I was just about to write my own macro and decided to search for a solution first. 🙂 When compiling, since I use Option Explicit at the top of each module, it got upset as there was no dimension statement for aString. So I added:
    dim aString as String ‘Text of the cell
    to the list of variable definitions at the top. After that, it compiled and worked perfectly. Thank you!

One Trackback

Post a Comment

You must be logged in to post a comment.

  • The Fine Print

    Thanks for reading Editorium Update (ISSN 1534-1283), published by:


    Articles © on date of publication by the Editorium. All rights reserved. Editorium Update and Editorium are trademarks of the Editorium.

    You may forward copies of Editorium Update to others (but not charge for it) and print or store it for your personal use. Any other broadcast, publication, retransmission, copying, or storage, without written permission from the Editorium, is strictly prohibited. If you’re interested in reprinting one of our articles, please send an email message to

    Editorium Update is provided for informational purposes only and without a warranty of any kind, either express or implied, including but not limited to implied warranties of merchantability, fitness for a particular purpose, and freedom from infringement. The user (you) assumes the entire risk as to the accuracy and use of this document.

    The Editorium is not affiliated with Microsoft Corporation or any other entity.

    We do not sell, rent, or give our subscriber list to anyone.