使用 VBA 防止 Excel 中的单元格重叠

Prevent Cell Overlap in Excel using VBA

提问by Ehudz

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.

我想知道是否有一种方法可以防止单元格的文本与相邻单元格重叠，而不必调整单元格本身的大小，或将单元格内容设置为换行。

回答by sbanders

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:

在 VBA 中，看起来像：

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

回答by Alain

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.

除了您提到的两种方法（重新调整单元格大小并将其设置为换行）之外，唯一的方法是在相邻单元格中添加文本。[编辑：我忘记了另一种方法，正如 Siddharth Rout 指出的那样，如果您将单元格格式设置为“缩小以适应”（在对齐>文本控制下），字体大小将自动缩小，以便内容适合单元格宽度。 ] 如果您只想在单元格边缘截断长文本，只有当文本流入的单元格具有自己的内容时，Excel 才会执行此操作。

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.

1. 循环遍历所有包含内容的单元格。
2. 如果它们还没有内容，则在水平相邻的单元格中放置一个空格。

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.

1. 循环遍历所有包含内容的单元格。
2. 检查与当前单元格相邻的单元格是否为空白。如果没有，您可以跳过此单元格并转到下一个单元格。
3. 将当前单元格的内容复制到空白列中的单元格（最好在新的临时工作表上）。
4. 告诉您的临时列自动调整大小（以找出所需的单元格宽度）。
5. 如果临时列的自动调整宽度大于源列的宽度，则单元格内容将溢出 - 因此您需要在相邻单元格中放置一个空格。

回答by Claude Fleurey

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:

可以在 VBA 中完成相同的过程：

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

回答by Jaycee

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

我只在重叠单元格旁边的单元格中放置了一个空格。它不再重叠。我将该单元格复制并粘贴到我的电子表格中

回答by Doug Dalager

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.

我只需在要阻止重叠的列的右侧插入一列，然后以较小的磅值输入一个字符。然后将它一直复制到电子表格中，并将列的宽度缩小到 0.5，使其几乎不明显。这样您就不会影响公式，并使电子表格更易于阅读。