I mentioned the width vs length only to demonstrate that here were an excess
number of characters required before the wrap takes place. This leaves some
of the characters hidden if they are below the magic threshold. What I need
is a method to wrap as soon as a character string exceeds what can be seen.

"Gord Dibben" wrote:

Jack

The width set at 40 does not govern how many characters will show before the
text will wrap or autofit.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

Depending upon the characters that could be more or less.

I tested with the letter "i" and got 93 before wrapping or autofit took place.

With "w" it took only 30.

Formatted as 10 point Arial Font.


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 08:46:00 -0800, Jack Greb <Jack
wrote:

I have a worksheet that has rows formatted to auto fit and cells formatted to
word wrap, but the cells do not auto adjust to input data. The column is set
to a width of 40. If the input string is over 39 characters long, the cell
stays 1 row high until the character length exceeds 51. I have tried the
attached code from Gord Dibben, but it does not resolve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub