We build a function to delete any spaces from the document at the end of a given document range.
Thanks for your interest
This content is part of a paid plan.
Delete spaces at the end of a range
Deleting spaces in a document is a trivial but essential task. This function encapsulates the steps necessary to delete any spaces at the end of a given document range. Then it can be used as a logical subtask in a bigger macro without needing to work through the details every time.
This article reimplements a previous macro to delete spaces at the end of a paragraph, and it demonstrates how the process differs when working with different input data. Another variation further generalizes the task to instead correct the spacing around a range rather than just deleting them, but the current article will focus on the latter subtask for a range instead of a paragraph.
Create the empty function
Open the VBA editor alongside Word using Option+F11 in Word for Mac (or Alt+F11 in Windows) and type the empty macro.
Each function needs a unique name. Including descriptive comments using a single quote character is strongly recommended.
What is a Range?
A Range in VBA is a general representation of any contiguous span of document content. It is defined as a virtual object (in the everyday sense) with many actions called "methods) and data called "properties" that allow us to manipulate it or the related content. Important properties for the current macro are its Start and End positions which define its extent in the document.
In contrast, a Paragraph is a VBA object representing a particular document range sandwiched between two paragraph marks. While some overlap exists between the two data types, Range variables work more with controlling the extent in the document and manipulating its content.
What is the parameter?
We usually design functions to receive external data. They're called parameters when defined in the function (inside the parentheses), but any data provided to the function in place of a parameter is called an argument. This function will accept a target range, but a more focused article implements the subtask using a Paragraph instead. We'll call our range parameter TargetRange.
Using a Range parameter is a little more general than a Paragraph, but the Range version requires a little more work because it can span any document content rather than just a paragraph.
What is the result?
Word VBA functions often carry out editing actions like a subroutine, but we also expect a final result summarizing what happened. Specifically, this function will return the number of spaces deleted. A typical number of deleted spaces would vary from zero to a few. Ten or more would be unusual. A Long number type allows huge counting numbers (up to about two billion) which is excessive for this function, but it's also a common numerical type, so we'll use it for this function.
The return type is specified with “As Long” on the header line after the parentheses. The returned result should be assigned to the function name sometime before the end of the function.
This placeholder results assigns an obvious default value of zero because we don’t know how many spaces were deleted yet. The move command below actually tells us if no spaces were spanned, so it already handles the default case.
Delete spaces at the end of a range
Even when I've already worked through the subtask logic in a different macro, experience has nudged me to create the function version from scratch. It's often easier to think through the details separately, and it helps recognize and avoid gotchas. Copying the steps over from another macro and modifying them in place is not incorrect, but the function steps will usually need to be tweaked. The general case often involves slightly different assumptions compared what was written inside the constraints of the original macro.
Define some useful variables
Declaring variables is not required in VBA, but it's a good practice. The most important variable is the Range rEnd. The SpacesMoved variable will temporarily store the function result.
Each variable requires a separate data type, or it will default to a generic Variant which can store anything. `I like to use a prefix "r" for most Range variables as a reminder of its data type, and the name rEnd reminds us that it works with the End of the given range. We can require variable declarations by including Option Explicit at the top of the macro module.
Using a working range variable avoids a leaky faucet (function). If we used the TargetRange parameter directly inside this function, any changes to the range would change the given range outside the function (see our introduction to functions article for more explanation).
It's also common to declare variables where they are used. Both approaches are clear in their own way. I just preferred collecting them into one place for this function.
Assign the working range variable
We assign our working range rEnd as a Duplicate of the target range.
A Range is a VBA object, so Set is required for the assignment.
Exclude the paragraph marks (optional)
Since a range variable can span any document content, it may include more than one paragraph mark at the end. We can remove all of them using the MoveEndWhile method.
MoveEndWhile literally moves the End position of the range across any characters specified in a character set. We're focused on paragraph marks, so we assign a paragraph mark character vbCr (from a table of miscellaneous constants) to the Cset option. Forward is the default direction, so we also need to assign the wdBackward constant (from another table of constants) to the Count option. The two options must be separated by a comma.
To trim or not to trim?
This step is more of a preference, since a literal interpretation of the macro would delete any spaces at the end of the range. If a paragraph mark is the last character, it's not a space, so one could argue that the function should immediately end with zero spaces deleted. However, a common use case would include a whole paragraph range which naturally ends in a paragraph mark. To avoid repeated qualifications, the rest of the function will assume any paragraph marks are trimmed.
Collapse the working range
We will delete any spaces at the end of the range. It's easiest to identify the spaces if we Collapse the working range toward that side.
Collapsing toward the start is the default, so we need to assign the wdCollapseEnd constant to the Direction option. Now, the empty working range is positioned just before the (first) paragraph mark and just after any potential spaces if they are present.
Extend the working range over any spaces
The working range is now empty, so we span any spaces before it. More specifically, we move the Start position of the range backward over any spaces using the MoveStartWhile method.
MoveStartWhile works almost the same as MoveEndWhile except it moves the Start position. MoveStartWhile does not change the End position of the Range, so if any spaces exist, the working range will span them after this step.
Get number of characters moved
We need to track how many spaces were spanned. Fortunately, the MoveStartWhile method already tells us how many characters it moved. We usually ignore this information in most macros, but this time we need to store the result in a variable.
The method returns how many characters the Start position moved (technically, as a Long number type). Positive values indicate forward movement, zero means it did not move, and negative values indicate backward movement. Earlier, we defined our SpacesMoved variable as a Long number type. By storing the result, so the data types match. We're using the MoveStartWhile method like a function, so VBA requires parentheses around the arguments.
Correct the sign of the move result
If any spaces were identified, the Start position moved backward in the document, so our result is negative. The number of spaces deleted later is conceptually either zero or a positive number, so we need the absolute value of the movement result.
Abs(…) is the standard absolute value function from general mathematics just rephrased for the computer world. Absolute value returns the positive version of whatever number is given to it. For regular numbers, it just drops a negative sign if it exists. The absolute value of zero is still zero.
We don't need to declare an extra variable just for this, so we stored the result back in the same variable.
Combine the steps (aside)
We could combine the two previous steps, but it's a little messy.
This command runs from the inside outward, but it looks too much like real programming. Let's summarize what happens:
- The MoveStartWhile method runs using the arguments given (inner parentheses).
- The result is returned, but VBA immediately passes it to the absolute value function (outside parentheses).
- The absolute value result is then stored in the variable (the equals = sign) overwriting the previous stored value.
It requires thinking more like a programmer, but it's also common for people that prefer concise macros. Two clear lines or a single compact, but harder to read line. Some people like driving on the interstate, and others prefer mint chocolate chip ice cream. Some people like similes, and others prefer mixed metaphors, so just pick your favorite lane.
Delete any spaces
We now delete any spaces contained in our working range rEnd, but we can’t just use the Delete method.
Uhhh … why not?
What if no spaces were found?
Delete will delete any spaces spanned by the range, which is what we want …
But if no spaces were found earlier—meaning rEnd is still empty at this point in the macro—the command will still delete the character to the right much like what would happen if we tapped the (forward) Delete key on the keyboard. Regardless of what that character is, but we don't want to delete it.
Condition to detect spaces
We need to verify whether any spaces are spanned by rEnd before deleting them. We could verify at least one space exists in the range since we know it would only contains spaces, but the validation is a little more complicated than it sounds due to some idiosyncrasies in how VBA treats empty ranges.
- The number of characters cannot be used because an empty range is also considered to contain one character.
- Detecting an empty space in a range can be confused with the character after the range depending on whether it's empty or not.
Fortunately, we can sidestep the problem this time because we've already stored how many spaces were spanned.
How many spaces were spanned?
The condition to check for any spanned spaces is simply whether SpacesMoved is bigger than zero.
This is just the greater than > symbol from general mathematics.
Conditional statement checking whether spaces were present
A rough conditional statement checking for spaces would look something like:
Putting the above condition and the delete command together, a tentative conditional statement is:
Looks good. Now—
Hold on.
We have another problem.
Delete a straggling space
A little Word feature rears its little gremlin head out of nowhere. Word often adjusts spacing for us as needed during manual editing. We don't pay much attention in real-time editing because it's often a useful feature, but for macros and functions, it's more annoying than helpful.
A rough conditional statement to correct for it would be:
Oh, that's look simple … and it is, in principle. The details are a little tricky though.
Did Word insert a new space?
Word may or may not insert the extra space depending on an editing setting, so we need to check before trying to delete it. What do we know about the extra space? What problems might we encounter?
These considerations assume we've identified some spaces to delete. Meaning, we're inside the above conditional statement getting ready to delete the identified end-of-range spaces.
- Before the delete step, we know rEnd spans some spaces.
- Since we're working with a general target range, another space could already exist outside it.
- After the delete step, we know the working range is empty.
- The extra space will not be inserted until after we delete the spaces which forces the correction to be done as a separate step.
- If Word added a space for us, it will be inserted just after the empty rEnd range.
- If we delete the working range, we cannot tell the difference afterward between a new space and one already present in the document before the delete step.
Like I said, annoying.
We have to make a decision from this information. Without getting bogged down in VBA muck, let's narrow it to more specific conditions.
- We need to know the character immediately outside the rEnd range before the delete step.
- We need to know whether a new space exists after the delete step.
What is the character immediately after the working range?
We need to know what character followed our working range before we delete the spaces.
The main problem with Word inserting the extra space occurs at the end of a paragraph. We don't want the function to leave a straggling space there, so we'll focus on that variation just to narrow the possibilities. We'll trust Word to correct the spacing elsewhere in a paragraph.
Is the next character after the delete step a space?
If we just deleted the spaces spanned by the rEnd range, we know the range is currently empty. Intuitively, the "next" character is the one immediately at the empty range position. The catch is VBA considers it to be the "first" character when the range is empty.
Revised conditional statement to detect a new space
We can revise our conditional statement with these more specific conditions.
Now, let's work through the details.
Get the next character after the working range
Before the delete step, we know the working range spans at least one space, so the correct next character range is literally found using the Next method.
The default unit is a character, so we can omit the Unit option. This method returns the range of the character. We need the text for the comparison, so we refer to the Text property of the character range.
We store the result in a conveniently named variable. The catch is we need the next character before the working range rEnd is deleted.
Get the first character after the working range
To get the first character, we refer to the Characters collection of the range variable.
The First property of the collection gives us the first character range.
The First property returns the range of that character, but we need its text for the comparison. We use the Text property of the character range.
We store it in a conveniently named variable, so the conditional statement below is clearer. We need to store this character after the original spaces are deleted.
Delete extra space condition one
Our first condition simply checks whether this first character is a space " ".
Yet another little issue pops up.
Delete extra space condition two
We only want to delete the extra space if Word reinserted it at an inappropriate location, but this is usually only a problem at the end of the paragraph.
Including this second condition is annoying, but it solves an exception. These conditions will be evaluated as a True or False (Boolean) value but only when used in a conditional statement.
Delete space compound condition
We could write some logic to correctly adjust the spacing anywhere, but that's overly complicated at this level (not hard, just a little messy). The most common issue is when Word reinserts an extraneous space at the end of a paragraph, so we'll focus on that case. Thus, our condition becomes checking for a new space but only when it's followed by a paragraph mark.
Both conditions must be True before we delete the extra space, so the compound condition requires an And operator.
Delete the space command
If a space exists, we just delete it with the Delete method.
We do not need to span the space because the Delete method will delete the first character to the right of the range when the range is empty. It's like tapping the (forward) Delete key on the keyboard when we have an insertion point (i.e., the blinking I-bar) in the document.
Delete straggling space conditional statement
Putting the conditional statement and the delete command into a quick conditional statement, we have:
It is annoying to include an extra conditional statement to take care of a single space reinserted by Word, but we're now sure no spaces exist at the end of the range at the end of a paragraph. We'll trust Word's automatic spacing corrections for a range positioned anywhere else in a paragraph.
Assign the return value
The above SpacesMoved variable stores how many spaces were spanned, so we assign it as the function result.
This assignment also works when no spaces were deleted because the MoveStartWhile method will return a value of zero in that case. This number does not include the extra space Word reinserted during the process.
Gotchas
What could go wrong?
Is the target range valid?
We often verify whether the data passed to the function is valid. A simple validation would check whether the argument actually refers to a valid document range. A more thorough explanation of such a validation check is given in our move sentence article, but we'll summarize it here.
Condition for whether the target range is valid
VBA assigns Nothing to any object variable that is not yet assigned to a valid document element. We cannot compare VBA objects with an equals = sign like we did with the FirstCharacter check earlier. Instead, we use the "Is" keyword. The condition for whether the target range is not yet assigned is:
If the target range argument is not yet assigned, we should exit the function.
But this is a function … so we need to assign an appropriate result.
Assign an invalid result for the number of deleted spaces
We should not leave the returned result to a default value assigned by VBA. The number of spaces deleted cannot be negative, so assigning a negative value will indicate an error occurred in the function.
Conditional statement validating the target range
The conditional statement checking for an invalid target range is:
With the assignment, anyone using the function can check if an error occurred. Any other action with an invalid Range variable will cause the macro to crash, so this validation should be included near the beginning of the function. The Exit Function command immediately quits the macro without running any further steps.
Let’s talk about the extra work
Generality usually comes at a price. Fortunately, the price is relatively small here if we're willing to trudge through some shallow swamp water. When creating functions and macros, we have to decide how they work.
Okaaay … sounds obvious.
Sometimes it's obvious like DeleteCurrentParagraph. Only one final result matches the implied action. In other functions and macros, certain details about how it works are more of a preference. Other details appear with testing.
Should we also delete spaces outside the range?
Do we worry about any spaces outside the end of the given target range? We didn’t have to worry about this question with the Paragraph version.
Generally speaking, it probably better to not change anything outside the given TargetRange since that's what they gave us. The name of the function is DeleteRangeEndSpaces, so it should probably do that and nothing extra without a good reason. If you choose to add this feature, include a clear comment at the top of the function explaining it.
Is it okay to trim the paragraph mark(s)?
Unfortunately, I broke the above rule for paragraph marks. Before anyone throws rotten eggs at me over the flagrant hypocrisy, let me explain.
Paragraph ranges are a common use case, so trimming paragraph marks seems consistent with the purpose of the function. If we want to be extra general, we could include an Optional parameter to give the user a choice. I would probably do so in my own function, but that's beyond the scope of this article.
Secondary issues solved while creating this function
Working with a target range instead of a more specific document element like a paragraph required some extra work. In addition to solving some previous quirks that also appeared in the paragraph version of the function, we had to decide on several more issues.
- We decided to not delete any spaces outside the end of the target range.
- A paragraph range is a common use case, so we decided to trim any paragraph marks at the end of the range.
- Since we're allowing any target range, we further needed to trim all the ending paragraph marks for the target range (avoids getting stuck on empty paragraphs).
- To keep the function simpler, we decided not to include an optional parameter to control whether the paragraph marks are removed.
We also solved at least two extra gotchas in the process:
- Using a general target range caused an extra problem handling the straggling space Word sometimes inserts.
- This issue brought up an idiosyncrasy with how Word treats the next character for empty and non-empty ranges.
- We solved the issue by deleting the extra space only at the end of a paragraph.
See what’s happening?
These considerations are more than simple changes one might expect when "just" changing the parameter from a Paragraph to a Range. None of them were particularly difficult to solve, but together, the function is messier to implement. I lean toward generality (within reason) for my own functions and macros, but this is why we initially created it using a Paragraph parameter.
Final delete range end spaces function
Collecting the various commands above, the final function to delete any spaces at the end of a given range is:
The basic features as well as many of the steps are the same as in the paragraph version. This version is more general since it accepts any Range, not just paragraphs, so it is preferable in that regard; but it's an interesting example of how small choices affect the complexity of the solution. Sometimes choosing the simpler solution is better.
Examples of using the function
Regardless of how complex the function was, using it is easy. In the examples below, we'll assume our main macro has a valid document range stored in a variable named SomeRange.
Ignore the returned result?
If we don't need to know how many spaces were deleted, we can just use the function like a subroutine to carry out the action.
We can also include the parameter name with the option assignment if we want to be extra clear. A colon equals := symbol is required when assigning an argument to its parameter name.
After either function call, SomeRange will not have any spaces at its end regardless of whether any were present or not. The function has no effect on any spaces already present outside the original SomeRange or any added by Word after the delete action.
Store the returned result?
If we need to know how many spaces were deleted (perhaps the main macro requires a later validation), we can store the value returned by the function.
VBA requires parentheses around the argument SomeRange since we're storing the result in a variable. We can also include the parameter name inside the parentheses if we want to be extra clear.
