So I was off working on a new bit of code today when an email came through:

“The tool is blanking out a cell when it’s not supposed to!”

Ack.

No big deal though. I fire up the project, load up the document in question and give it a run.

Sure enough, a cell that contains some text and a merge field (under my control) is being completely cleared out. Only the merge field should be being cleared.

I dig a little more and eventually narrow the culprit down to a single function.

Private Function MyFunc(Rng as Range)
   ....
   Rng.Delete
   ....
End Function

When I hit that Rng.Delete line, the ENTIRE CELL was being cleared. Including text that had nothing to do with my merge field (other than the fact that it was also in the same table cell).

After a bit more single stepping through code, I discovered that the Range being deleted was actually empty. In other words, it’s Start position was the same as it’s End position.

Now, I’ve know from dealing with Word for far too many years that when you’re working with an empty range like that, there are a good number of things that don’t work quite right. Many will throw errors, but I was getting no error here.

Then it hit me. As a test, I added a few chars of text at the VERY END of the cell, after my merge field.

Sure enough, everything worked, even though the range being deleted was still empty.

The End-of-Cell Marker.

Word stores an End-of-Cell marker character at the end of each cell in a table. The marker is a Chr(7) if you retrieve it, but you wouldn’t normally ever even bother with it.

However, if you end up with an empty Range object that is situated right at the end of a cell, then that Range is effectively pointing at the End of Cell marker, and if you invoke the Delete method on the Range, you’ll end up invoking it on the End of Cell Marker.

And what happens if you try and delete an End of Cell Marker?

Well, duh. The cell is cleared.

Moral of the Story

The short version of all this is simple:

If you plan on deleting a Range, make sure that the Range.Start <> Range.End before you do:

If Range.Start <> Range.End Then
   Range.Delete
End If

Technically speaking, if start = end, then there’s nothing in the range anyway and so there shouldn’t be anything there to delete. But doing so can definitely have detrimental effects in some cases.

And, as usual, your mileage may vary.