Prevent Cell Overlap in Excel using VBA

Question

I am wondering if there is a way to prevent a cell's text from overlapping into the adjacent cell(s) without having to resize the cell itself, or set the cell contents to wrap.

Solution 1

The only way aside from the two methods you've mentioned (re-sizing the cell and setting it to wrap) is to have text in the adjacent cell. [edit: There's one other method I forgot, as Siddharth Rout pointed out, if you format cells as "Shrink to fit" (under alignment>text control), the font size will automatically shrink such that the content fits inside the cell width.] If you just want long text to get cut off at the cell's edge, Excel will do this only if the cell the text would otherwise flow into has content of its own.

The fastest way to enforce this automatically would be to:

1. Loop over all cells with content.
2. Place a blank space in the horizontally adjacent cells if they do not already have content.

Note that you should either do this to both cells to the left and right of cells with overflowing content, or check whether the text is left aligned or right aligned to help decide which of the two horizontally adjacent cells will need to be filled.

If you ONLY want to do this for cells that would have otherwise overflowed, then you have to do something more complicated. You will have to:

1. Loop over all cells with content.
2. Check if the cell adjacent to the current one is blank. If not, you can skip this cell and move on to the next one.
3. Copy the content of the current cell to cell in a blank column (preferably on a new temporary sheet).
4. Tell your temporary column to auto-size (to find out the desired width of the cell).
5. If the auto-size width of the temporary column is larger than the source column's width, then the cell content is going to overflow - so you need to place a blank space in the adjacent cell.

Solution 2

Good morning. There is a way to do this cleanly:

• Select Range
• Right Click > "Format Cells" > "Alignment" Tab
• In Text Alignment area, select "Fill" from "Horizontal" Drop Down Menu

In VBA, looks something like:

Sub AwayWithYouConfusingOverlap()
    Range("A1").HorizontalAlignment = xlFill
End Sub

Solution 3

I discovered lately a clean method to prevent overlapping text from contiguous cells to appear:

• Select range
• right click > format cell > alignment > check box wrap

The result may look like ugly, since different lines may display with different height. However it's still possible to fix all by forcing all the lines to have same height:

• Select range
• Right click the leftest column (which holds the row number) > row height > enter the desired value.

That's it ...

I hope that my French will not obscure my English, at least not too much.

---

This solution has the advantage to also work with printing (if you want to prevent overflows into additional blank pages) because you do not need additional columns.

The same procedures can be done in VBA with:

With Range("A1:H100")
    .WrapText = True ' enable wrapping, will autoformat height
    .rows.RowHeight = 12.75 ' set back to default height, overflow is removed
End With