CODEWITHSUNDEEP
vbaexcel
Alegro
Alegro

Reputation: 7966

AutoFit doesn't work with wrapped text

I have one Chr(10) in the cell 

cell.WrapText = False
cell.EntireRow.AutoFit ' AutoFit works
' ------------ but:
cell.WrapText = True
cell.EntireRow.AutoFit ' AutoFit works only if the cell has less then five lines.

If I add some characters (one line more) - AutoFit doesn't work. Text is cutted on the first and last line.
ver - famous excel 2010

Upvotes: 3

Views: 26558

Answers (2)

Qudsia
Qudsia

Reputation: 53

A related solution in case it helps someone. I have data with 'Wrap Text' on and line breaks in cells that come from copying data from formulas that used 'Char(10)'. I need the line breaks.

For example, data in a cell should show like this:
City: New York (1950)
Country: USA

But it showed something like this:
City: New
York (1950)
Country:
USA

Selecting the column and clicking to autofit resulted in slight improvement:
City: New York
(1950)
Country: USA

Repeating manual auto-fit a 2nd time brings text to desired format.
If there is more space-separated text in a line, it takes that many more auto-fit attempts, and eventually format is fully corrected.

So in my VBA code, I replicated this and repeated auto-fit code the number of times I knew would fix my issue: 

'Adjust column Width
        wsSched2.Columns("F:CZ").EntireColumn.AutoFit
        DoEvents
        wsSched2.Columns("F:CZ").EntireColumn.AutoFit
        DoEvents
        wsSched2.Columns("F:CZ").EntireColumn.AutoFit

(It may work without 'DoEvents'. I haven't tried)

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

It depends on how big the data is and what is the width of the column. Reason being the max height a row can expand is to 409.5 (546 pixels). If you manually increase the height of the row, you will notice that after a particular height, you will not be able to increase the height of the row. Check what is that height :) Same is the concept with the width. This is applicable to both rows and columns. In case of columns the max width is 254.86 (1789 pixels)

I used this code for demonstration purpose.

Option Explicit

Sub Sample()
    ActiveCell.WrapText = True
    ActiveCell.EntireRow.AutoFit
    MsgBox ActiveCell.RowHeight
End Sub

See this screenshot

This works

enter image description here

This doesn't

The row has reach it's maximum. It cannot go beyond that.

enter image description here

The best way to handle this is to increase the width of the column.

FOLLOWUP

The reason why it was not working is because you had multiple columns with data in that row and you were trying to just set the WrapText of 1 cell and hence it was not working. When you have multiple columns of data you have to use cell.EntireRow.WrapText = True instead of ActiveCell.WrapText = True

Upvotes: 4

Related Questions