excel 2016 wrap text problem

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Attachments

  • text.png
    text.png
    28.8 KB · Views: 14

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,678
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top