vba 查找太小而无法显示内容的文本单元格

Find Text Cells That Are Too Small to Display Contents

提问by Craig

I have a worksheet with cells that may contain text that sometimes exceeds the width and height of the cell. If I don't notice it and adjust the row height, the text shows up as cutoff. Does anyone have a VBA snippet or tool that can locate these cells so I can adjust them? Thanks!

我有一个包含单元格的工作表，其中可能包含有时超过单元格宽度和高度的文本。如果我没有注意到并调整行高，则文本显示为截止。有没有人有可以定位这些单元格的 VBA 代码段或工具，以便我可以调整它们？谢谢！

回答by Mike Rosenblum

Identifying which column is too wide would be tricky, especially since the text within the cell can have different fonts and/or font sizes. It is much easier to automatically size the columns by making use of the Range.AutoFitmethod.

确定哪一列太宽会很棘手，特别是因为单元格内的文本可能具有不同的字体和/或字体大小。使用Range.AutoFit方法自动调整列大小要容易得多。

As an example, you can call the AutoFit method as follows:

例如，您可以按如下方式调用 AutoFit 方法：

Dim myRange As Excel.Range
Set myRange = Application.Range("A1:C3")
myRange.Columns.AutoFit

The Range.AutoFit method can be a bit too aggressive, however, resulting in columns that are too narrow. Therefore, you might want to create a method that establishes a minimum column width:

然而， Range.AutoFit 方法可能有点过于激进，导致列太窄。因此，您可能想要创建一个建立最小列宽的方法：

Sub AutoFitColumns(theRange As Excel.Range, minColumnWidth As Double)
    theRange.Columns.AutoFit

    Dim column As Excel.Range

    For Each column In theRange.Columns
        If column.ColumnWidth < minColumnWidth Then
            column.ColumnWidth = minColumnWidth
        End If
    Next column
End Sub

The above could be called as follows, to autofit the columns, but with a minimum width of 8.5:

上面可以调用如下，以自动调整列，但最小宽度为 8.5：

Dim myRange As Excel.Range
Set myRange = Application.Range("A1:C3")
Call AutoFitColumns(myRange, 8.5)

You can also autofit the Rows of the Range. This is needed less often, but to do so you'd use something like:

您还可以自动调整范围的行。很少需要这样做，但要这样做，您可以使用以下内容：

myRange.Rows.AutoFit

Hope this helps!

希望这可以帮助！

Update: Reply to Craig's Comment:

更新：回复 Craig 的评论：

Thansk Mike. This spreadsheet is used as sturctured input to a web interface generator so the column widths vary and shouldn't be adjusted. I'm really just looking for something to scan a sheet for any cells where the text displayed is wider than what the cell will allow to fit, thereby needing the row height sized larger. I'm not looking for a process to make the adjustment, just find them since they are easy to overlook. Any ideas on that?

谢谢迈克。此电子表格用作 Web 界面生成器的结构化输入，因此列宽会有所不同，不应进行调整。我真的只是在寻找一些东西来扫描任何单元格的工作表，其中显示的文本比单元格允许的宽度宽，因此需要更大的行高。我不是在寻找进行调整的过程，只是找到它们，因为它们很容易被忽视。对此有何想法？

Well, clearly you want to do twothings: (1) identify which cells are being cut off, and then (2) correct these cutoffs.

嗯，很明显你想做两件事：(1) 确定哪些单元格被切断，然后 (2) 纠正这些切断。

I do understand your desire to do this in two distinct stages, but step (1) is almost impossible to do correctly across all circumstances with Excel because not only can row heights and column widths vary, but text wrapping can be on or off, and the font can be a non-proportional font in any number of potential styles and/or sizes.

我确实理解您希望在两个不同的阶段执行此操作，但是几乎不可能在 Excel 的所有情况下都正确执行步骤 (1)，因为不仅行高和列宽可以变化，而且文本换行可以打开或关闭，并且字体可以是任意数量的潜在样式和/或大小的非比例字体。

In short, there will be no way to reliably identify which cells are being cut off, not without an incredible amount of work. It could be mitigated, however, if you restrict the spreadsheet to only one font style and use a non-proportional font. If you do this, then you could simply compare the column width to the length of the text within the cell. This is because the Excel.Range.ColumnWidthproperty returns its width calibrated so that one unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

简而言之，如果没有令人难以置信的大量工作，将无法可靠地识别哪些细胞被切断。但是，如果您将电子表格限制为仅一种字体样式并使用非比例字体，则可以减轻这种情况。如果您这样做，那么您可以简单地将列宽与单元格内文本的长度进行比较。这是因为Excel.Range.ColumnWidth属性返回其经过校准的宽度，以便一个单位的列宽等于 Normal 样式中一个字符的宽度。对于比例字体，使用字符 0（零）的宽度。

Therefore, for simplecases, where word wrap is not employed and the font is in the Normal style, and, ideally, the Normal font is a non-proportional font, then you could loop through all cells in the range looking for where the value held is longer than the column width:

因此，对于不使用自动换行且字体为 Normal 样式的简单情况，理想情况下，Normal 字体是非比例字体，然后您可以遍历范围内的所有单元格以查找值的位置持有比列宽长：

Dim myRange As Range
Set myRange = Application.Range("A1:E5")

Dim cell As Excel.Range
For Each cell in myRange.Cells
    If Len(CStr(cell.Value)) > cell.ColumnWidth Then
        MsgBox "The cell at " & cell.Address & " has text that is too long!"
    End if
Next cell

But now here comes the next part... How will you correct this? If, again, you have a very simple situation where word wrap is not employed and the font is in the Normal style, and, ideally, the Normal font is a non-proportional font, then you have a few options:

但是现在是下一部分...你将如何纠正这个问题？再次，如果您有一个非常简单的情况，即不使用自动换行并且字体为 Normal 样式，并且理想情况下 Normal 字体是非比例字体，那么您有几个选择：

(1) Set the column width to match the cell's value length:

(1) 设置列宽以匹配单元格的值长度：

Dim myRange As Range
Set myRange = Application.Range("A1:E5")

Dim cell As Excel.Range
For Each cell in myRange.Cells
    If Len(CStr(cell.Value)) > cell.ColumnWidth Then
        cell.ColumnWidth = Len(CStr(cell.Value))
    End if
Next cell

(2) Auto-fit the Column:

(2) 自动调整列：

Dim myRange As Range
Set myRange = Application.Range("A1:E5")

Dim cell As Excel.Range
For Each cell in myRange.Cells
    If Len(CStr(cell.Value)) > cell.ColumnWidth Then
        cell.Columns.AutoFit
    End if
Next cell

But if we are going to auto-fit the column, then it is much easier to just call it directly, without checking the column widths first, because not only will the widths be corrected for all cells at one time, but the Range.AutoFitmethod can handle any font, including non-proportional fonts, in any style.

但是如果我们要自动调整列，那么直接调用它会容易得多，而无需先检查列宽，因为不仅会一次校正所有单元格的宽度，而且 Range.AutoFit方法可以处理任何样式的任何字体，包括非比例字体。

Therefore, going directly to the auto-fit approach, we have two options: either autofit the columns, or autofit the rows. Based on your most recent quote, it sounds like you want to re-size the rows:

因此，直接进入自动适应方法，我们有两个选择：要么自动适应列，要么自动适应行。根据您最近的报价，听起来您想重新调整行的大小：

I'm really just looking for something to scan a sheet for any cells where the text displayed is wider than what the cell will allow to fit, thereby needing the row height sized larger.

我真的只是在寻找一些东西来扫描任何单元格的工作表，其中显示的文本比单元格允许的宽度宽，因此需要更大的行高。

For this I would employ text-wrapping and then autofit the rows. For example,

为此，我将使用文本换行，然后自动调整行。例如，

Dim rng As Excel.Range
Set rng = Application.Range("A1:E5")

With rng.Rows
    .WrapText = True
    .AutoFit
End With

I think that these are about all your options. In the end, what you want to do and what Excel is capable of doing might not match exactly, but I thinkyou should be able to get done what you need to? Let us know if it does the trick...

我认为这些是关于你所有的选择。最后，您想要做什么和 Excel 能够做什么可能不完全匹配，但我认为您应该能够完成您需要做的事情？让我们知道它是否有效...

-- Mike

——迈克