如何使用VBA检测Excel单元格中不适合的值？

Question

我们正在使用各种工具生成很长的Excel工作表，这些工作表必须经过审查，并在工作流程中进一步用作输入。一些单元格太小，无法容纳文本的问题。因此，阅读工作表的人和程序将不会看到相同的数据。

对于包含自动换行文本的合并单元格，当Excel未正确调整行高时，通常会出现这种情况。但也有其他情况:例如，当某些列显式设置了width时，这对于长值来说是不够的。

|Group|Def1 |Subgroup|Definition| Id   |Data |Comment   |
|-------------------------------------------------------|
| G1  |     | G1-1   |Important |G1-1-1|...  |          |
|     |Long |        |about G1-1|G1-1-2|.....|........  |
|     |text |-------------------------------------------|
|     |about| G1-2   |Another   |G1-2-1|...  |          |
|     |group|        |important |G1-2-2|...  |long comme|
|     |G1.  |        |text about|G1-2-3|     |          |
|-------------------------------------------------------|

在这里，“定义”和“注释”中的一些单元格并不完全可见。有没有什么方法可以通过编程找到这样的单元格？

Answer 1

要检测这些单元格(我不是在讨论修复问题)，可以使用Range对象的Text方法。

例如，Range("A1").Value可能是123456789，但是如果它被格式化为数字，并且列不够宽，那么Range("A1").Text将是"###“(或者单元格中可以容纳多少个#符号)。

Answer 2

这是我以前用过的一个技巧：

With Columns("B:B")
    oldWidth = .ColumnWidth ' Save original width

    .EntireColumn.AutoFit
    fitWidth = .ColumnWidth ' Get width required to fit entire text

    .ColumnWidth = oldWidth ' Restore original width

    If oldWidth < fitWidth Then
        ' Text is too wide for column.
        ' Do stuff.
    End If
End With

当然，这将一次应用于整个列。您仍然可以通过将单元格逐个复制到一个虚拟列并在那里执行AutoFit测试来使用它。

但可能对你更有用的是我之前对这个问题的回答：Split text across multiple rows according to column width。它描述了一种确定任意给定单元格中文本宽度的方法(并将其与单元格的实际宽度进行比较，以确定文本是否适合)。

如果您的某些单元格的高度足以显示2行或更多行文本，那么您可以使用类似于my previous answer中描述的方法，首先使用.EntireRow.AutoFit确定字体的高度，使用.RowHeight确定单元格中适合的行数，然后使用上一个问题中的方法确定文本是否适合该宽度的单元格中的行数。

Answer 3

取消合并工作簿中的所有单元格，并对列使用Thisworkbook.sheets("Name").rows(index).entirerow.autofit。除了美观的原因，保留合并后的单元格有什么用呢？只考虑“基本单元格”的值(左上角)。