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!

 

[jasonb75]

Sometimes there's so much text that have to merge rows also manually to fit it.

From the screen capture it looks like you have merged several columns as well. Autofit only works on single cells. If you have merged them (rows or columns) then you can only resize manually.

Instead of merging columns you can either use 'Centre across selection' (top left dropdown on the formatting tab shown in your screen capture) or allow the text to flow into the empty cells by default. Merging rows should not be necessary.

 

[xenios]

From the screen capture it looks like you have merged several columns as well. Autofit only works on single cells. If you have merged them (rows or columns) then you can only resize manually.

Instead of merging columns you can either use 'Centre across selection' (top left dropdown on the formatting tab shown in your screen capture) or allow the text to flow into the empty cells by default. Merging rows should not be necessary.

Thank you for the answer. Yes, it merges the columns.
The thing is, it comes up like this automatically from the system.

Then, may be I have to unmerge everything first and then use 'Centre across selection' ?
But how not to do it all manually?

 

[jasonb75]

Then, may be I have to unmerge everything first and then use 'Centre across selection' ?

Looking at the example that you provided you don't need to use Centre across selection, the default setting would be adequate as the other cells would be empty once you unmerge them.

But how not to do it all manually?

You could select all and unmerge (use the macro recorder if you want it coded for future use). Note that this will unmerge all cells in the sheet, not just the notes.

 

[xenios]

Looking at the example that you provided you don't need to use Centre across selection, the default setting would be adequate as the other cells would be empty once you unmerge them.

You could select all and unmerge (use the macro recorder if you want it coded for future use). Note that this will unmerge all cells in the sheet, not just the notes.

To tell the truth, I would prefer not to touch anything else merged.
But just tried now, and still Center across selection unfortunately not working in this case

 

[jasonb75]

But just tried now, and still Center across selection unfortunately not working

It works fine for me. Note that you must unmerge the cells first then select multiple cells to centre across.

I thought that you might be able to get what you need by unmerging but without centring, but as your text is wrapped it must be centred to work.

Is it only ever going to be the last entry in sheet that this needs to be applied to? i.e. notes at the bottom of the page?

 

[jasonb75]

Following up, there is a fix in this thread that you might be able to make use of.

Auto Fit Row Height Of Merged Cells

Hi. Is there any way i can make excel autofit row height of merge cells?

www.mrexcel.com

I haven't looked at the code there in detail but it will need editing to match your sheet. I don't have time to look at it now but if it is something that you can use then I'll look at the changes for you.

 

[xenios]

It works fine for me. Note that you must unmerge the cells first then select multiple cells to centre across.

I thought that you might be able to get what you need by unmerging but without centring, but as your text is wrapped it must be centred to work.

Is it only ever going to be the last entry in sheet that this needs to be applied to? i.e. notes at the bottom of the page?

For me it still eats the last sentence. (attached)

I can make it to be the last.

 

[jasonb75]

Did you double click the row height line between rows 82 and 83 afterwards?
The height adjustment is not automatic as soon as you apply the format change, you still need to double click to autofit.

Also, see the link in post 7. Do you think it is something that you could use?

 

[xenios]

Sub MergedAreaRowAutofit()
Dim j As Long
Dim n As Long
Dim i As Long
Dim MW As Double 'merge width
Dim RH As Double 'row height
Dim MaxRH As Double
Dim rngMArea As Range
Dim rng As Range

Const SpareCol As Long = 26
Set rng = Cells(Rows.Count, "A").End(xlUp)
Rows(rng.Row).WrapText = True

With rng
For j = 1 To .Rows.Count
'if the row is not hidden
If Not .Parent.Rows(.Cells(j, 1).Row) _
.Hidden Then
'if the cells have data
If Application.WorksheetFunction _
.CountA(.Rows(j)) Then
MaxRH = 0
For n = .Columns.Count To 1 Step -1
If Len(.Cells(j, n).Value) Then
'mergecells
If .Cells(j, n).MergeCells Then
Set rngMArea = _
.Cells(j, n).MergeArea
With rngMArea
MW = 0
If .WrapText Then
'get the total width
For i = 1 To .Cells.Count
MW = MW + _
.Columns(i).ColumnWidth
Next
MW = MW + .Cells.Count * 0.66
'use the spare column
'and put the value,
'make autofit,
'get the row height
With .Parent.Cells(.Row, SpareCol)
.Value = rngMArea.Value
.ColumnWidth = MW
.WrapText = True
.EntireRow.AutoFit
RH = .RowHeight
MaxRH = Application.Max(RH, MaxRH)
.Value = vbNullString
.WrapText = False
.ColumnWidth = 8.43
End With
.RowHeight = MaxRH
End If
End With
ElseIf .Cells(j, n).WrapText Then
RH = .Cells(j, n).RowHeight
.Cells(j, n).EntireRow.AutoFit
If .Cells(j, n).RowHeight < RH Then _
.Cells(j, n).RowHeight = RH
End If
End If
Next
End If
End If
Next
.Parent.Parent.Worksheets(.Parent.Name).UsedRange
End With
End Sub

This one works fine if I delete everything and leave only the text that would start from A1

Otherwise didn't manage to make t to work.

The las one that jann6628
said it's finally working, didn't manage to make it work, had errors all the time.