excel 2016 wrap text problem

[xenios]

Hi All!
Searching all over the internet it seems the problem can't be solved, as it is just like that. But may be someone still can find a solution.
In workbook, several sheets ( a lot of sheets) have free text with merged cells ( columns) and not all the text is visible. ( attached). Autofit cell height is not working.
Example of the file here test.xlsx

So far the only solution was to drag the cell down until everything is visible manually.
Rows are always different. Column starts from A
Sometimes there's so much text that have to merge rows also manually to fit it.

May still there's way around doing it all by hand?
Thank you!

 

[xenios]

I don't think that you will have to do manual with anything like that, the number of lines of text in the cell exceeds the number of lines of text that will fit on the screen. The maximum height for autofit can not be taller than the screen.

It might work by reducing the zoom level to make it fit on the screen, I'll have a go at that later and see what happens.

Ok, thank you.

 

[jasonb75]

I've tried a few things without success, I don't think that it is possible to do automatically fit anything that will not fit in one cell. The limit on the maximum row height (or column width) is based on zoom being set at 100% so my idea of reducing it didn't work.

There is no way (that I can find) of seeing if the text is too big to fit the cell so that would mean merging 2 rows on every sheet, not just the problem ones. This would also mean that you would back to where you started (nothing will autofit).

At best, it might be possible to identify the sheets where the row is set to maximum height so that you don't need to check every one. Would that be helpful, and if so how would you want that information presented to you?

 

[xenios]

Thank you,
Yes, it would be helpful.
I can create an extra sheet with name text, and it can make a list of sheet names with this problem, if this can work this way.

 

[jasonb75]

This will add a new sheet at the end and list the sheets where the row height is set to maximum with a hyperlink to the relevant cell.

Option Explicit
Sub xenios_test()
Application.ScreenUpdating = False
Dim ColWidthA As Double, c As Range, MCell As Range, ws As Worksheet, listsheet As Worksheet, n As Long
Set listsheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
listsheet.Range("A1").Value = "Sheet Name"
For Each ws In ThisWorkbook.Worksheets
    With ws
        Set c = .Cells(.Rows.Count, 1).End(xlUp)
        Set MCell = c.MergeArea
        ColWidthA = .Range("A:A").ColumnWidth
        MCell.UnMerge
        .Columns("A:A").ColumnWidth = 200
        c.EntireRow.AutoFit
        If c.RowHeight > 409 Then
            With listsheet
                .Hyperlinks.Add .Range("A2").Offset(n), "#'" & ws.Name & "'!" & c.Address, , , ws.Name
            End With
            n = n + 1
        End If
        MCell.Merge
        .Columns("A:A").ColumnWidth = ColWidthA
    End With
Next ws
listsheet.Range("A1").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

 

[xenios]

Great!!! Thank you