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.

Answer 1

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

Answer 2

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.