How to detect values that do not fit in Excel cells, using VBA?

Question

We are generating long Excel sheets using various tools, which have to be reviewed and used as input further down in the workflow. The problem that some cells are too small for texts they contain. So humans and programs that are reading the worksheets will not see the same data.

This is usually true for merged cells containing auto-wrapped texts, when Excel does not adjust the row height properly. But there are also other cases: for instance, when some columns have width explicitly set, which is not enough for long values.

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

Here, some cells in "Definition" and "Comment" are not fully visible. Is there any method to find such cells programmatically?

Answer 1

To detect these cells (I'm not talking about fixing the problem), you could use the Text method of a Range object.

For example, Range("A1").Value might be 123456789, but if it's formatted as Number and the column is not wide enough, Range("A1").Text will be "###" (or however many # signs fit in the cell).

Answer 2

Here's a trick I've used before:

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

Of course this will apply to an entire column at a time. You can still use this by copying cells over one by one to a dummy column and do the AutoFit test there.

But probably more useful to you is an earlier answer of mine to this question: Split text across multiple rows according to column width. It describes a method to determine the width of the text in any given cell (and compare it to the cell's actual width to determine whether the text fits or not).

EDIT Responding to your comment: If some of your cells are tall enough to show 2 or more lines of text, then you can use a similar approach as described in my previous answer, first using .EntireRow.AutoFit to determine the height of the font and .RowHeight to determine how many lines fit in the cell, then figuring out whether the text can fit in that number of lines in a cell of that width, using the method of the previous question.