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.

Venice

Would Shrink to fit option do? – Companion

Answer 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.

Filler

Answer 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

Hhour

Only problem with this is that if the text is short enough, it WILL duplicate until it has filled out the cell. Still, it serves my needs well enough – Palladous

Answer 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

Surveillance

Answer 4

I put just a space in the cell next to the overlapping cell. It no longer overlaps. I copy & paste that cell all over my spreadsheet

Benue

Answer 5

I simply insert a column to the right of the one I want to stop from overlapping, and enter one character at a small point size. Then copy that all the way down the spreadsheet, and shrink the width of the column to 0.5 so that it is barely noticeable. This way you don't affect formulas and you make your spreadsheet easier to read.

Gentle

Answer 6

I solved my issue by throughing following steps:

1. Select Range (For my case, I have selected all area by pressing ctrl+A)
2. Right-Click > "Format Cells"
3. Go to "Alignment" Tab
4. From the "Text Alignment" area, select the "Justify" option from the "Horizontal" Drop Down Menu
5. Then press ok

Before It was:

Then It is:

Seibel