Trim Blank Space from a Range

Trimming any blank space makes the macro easier to create since we don’t need to think about as many exceptions for our macros. To simplify the explanation, we’ll focus on trimming blank space from the right side of a range, but a very similar argument applies to trimming them from the left side.

What’s the plan?

We want to remove all spaces, paragraph marks, and optionally tabs off one end of a range—

"Oh that’s easy …"

Just hold on a second. This one seems immediately obvious if you’ve been tinkering around with VBA in Word (or following this blog) for a while. Just use a quick command like so.

The command removes any spaces or paragraph marks from the End of a range.

And … we’re done, right?

Well …

It works fine as long as at least some regular (non-blank) text is spanned by the range.

What’s the problem?

The problem occurs when an empty range or one that contains only spaces or tabs is passed to the function. The above command will just keep moving the range position until it finds some regular text.

Doesn’t quite work …

More obvious solutions leave something to be desired.

Detect empty range?

We need a step to catch and correct blank ranges. We could just be direct and test whether the range is empty with something like this.

But this doesn’t catch when only spaces are in the range.

Detect space-filled paragraph?

Detecting an empty range directly doesn’t catch a blank paragraph (as opposed to an empty paragraph), so do we add yet another test for this?

That version would be a little more complicated. For example, we could iterate through each spanned character and check whether it’s a space, tab, or paragraph mark?

For now, ignore the loop stuff since I haven’t properly introduced them. The main point here is somehow tracking whether we ever encounter a regular character in the range and then make a decision on what to do.

I suppose that would work, but it’s just clumsy and clunky and quite a few more steps than initially expected to complete such a simple task.

Better solution?

Sometimes we just need to sit on our hands and not implement the first idea that comes to mind because we can catch both cases with a much simpler test. That’s why you have me along for the ride, right?

Create the function skeleton

Start by creating the function skeleton.

What parameters to use?

We accept the target range rTarget to trim. This variable is not changed during the function (normal range variables could be changed; see the article on ranges for a brief explanation).

Return type

We return the trimmed range.

Create duplicate working range

We want to avoid unintended changes to the rTarget range, so we immediately create a duplicate of it which we call r just to keep the name short.

Now our working range r is independent of the given range rTarget. That way a user is not unpleasantly surprised if the given range is changed during the macro.

Trim the blank space

As previously mentioned, we move the End of the range backward across any blank space using a quick MoveEndWhile command.

We specified the character set as a space plus a paragraph mark special character. I left off vbTab since I rarely use them in my novels, but you could just literally add it with + vbTab to the Cset characters if you prefer.

Since we specified the wdBackward option for Count, the command removes blank space from the End of the working range.

This would work fine by itself as long as at least some non-blank text is spanned. If not, the command can overshoot the starting range and end up on a previous paragraph.

Catching the overshoot

An easy method to catch when the MoveEndWhile command overshoots the beginning of the range is to check the resulting range position against the initial range. Fortunately, we created a duplicate of the initial target range, so rTarget still spans the original text range.

Recall the Start and End positions of a range are literally character positions (as Long values which allow bigger numbers than Integers) as counted from the beginning of the document.

If we moved too far, the End position of the working range will have moved before the Start position of the initial target range.

Why?

We removed all blank space from the initial range until no characters were left, so the MoveEndWhile command kept looking backward in the document as commanded. It just went too far, so the comparison check is:

What is a collapsed range?

We need a slight detour for a moment.

The Start and End positions of a collapsed range are the same, so no text (or any document content) is spanned by the range. We can achieve this manually by assigning the same document position to both Start and End positions of a range.

Correcting the overshoot

If we moved too far, the result should be a collapsed range at the beginning of the initial target range.

Why?

The “trimmed” range cannot logically occur before the Start position of the given target range, so we don’t let it.

How do we correct the working range position?

We just need to set the working range to the starting position of the initial target range.

More specifically for this version trimming on the right side of the range, we assign the Start position of the initial range to both the Start and End positions of the working range r.

The SetRange command is just a shorthand method to reassign the range with two parameters Start and End. It just saves a single step since we could also just assign the two values manually.

Corrected trimmed range

Insert this command into the above If statement to make the range correction.

Gotchas

What could go wrong?

This function is safe by itself since we’re not actually deleting or changing anything in the document just removing any blank space from the given range.