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?

Agriculture

I vaguely remember @SiddharthRout (it may have been someone else) posting an answer to a very similar question recently. Hopefully that person will post their solution here. – Victory

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

Harm

I think the Text just returns the value when the cell contains a string. It does not detect anything. However, for dates and numbers it works just as you described, and this can be useful, thank you. – Agriculture

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.

Profitsharing

Thank you. AutoFit tends to unwrap long strings and to extend the column width even if there is enough vertical space for each value. In my examples your method gives false positive nearly in every cell. – Agriculture

What examples?! Care to share them with us? – Fumigant

The real life examples are huge and contain customers' data, but I have added a simple illustration to the question. – Agriculture

Answer 3

Unmerge all cells in the workbook and use Thisworkbook.sheets("Name").rows(index).entirerow.autofit And the same for columns. What's the use of keeping the merged cells anyway except for esthetic reasons? Only the value of the "base cell" is taken into account (upper left).

Cranny

Thank you. The resulting tables are used by humans, so aesthetic reasons cannot be ignored. Avoiding merging as much as possible is a very good suggestion though. – Agriculture

Answer 4

I bumped into the same problem today. I am trying this trick to dodge it. Perhaps, it might be useful for you. It is coded to deal with one column width merging areas:

'Sheet2 is just merely support tool no data sheet in ThisWorkbook
With Sheet2.Range(target.Address)
    target.Copy
    .PasteSpecial xlPasteAll
    .UnMerge
    If .MergeArea.Count > 1 Then .UnMerge
    .ColumnWidth = target.ColumnWidth
    .Value = target.Value
    .EntireRow.AutoFit
    target.MergeArea.EntireRow.RowHeight = _
         1.05 * .RowHeight / target.MergeArea.Rows.Count
    .ClearContents
    .ClearFormats
End With

Unfortunately, if there are several columns with merged cells like this, perhaps their mutual needed height will collide with each other and extra code will be needed for restoring harmony. Looks like an interesting piece of code. Wish you find this helpful.

Lykins