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