Word wrapping in Excel

[Guest]

Is there a way to override a manual row height setting, if you want word wrap
to work automatically in Excel?

We are setting up a template for users in our organisation to use, and want
this to happen automatically (ie. we don't want staff to have to adjust row
height themselves).

We have selected the rows (whose height had been previously altered
manually) and we chose Format Cells Alignment - Word Wrap on. We've also
chosen the Format, Row, AutoFit command. However, all the text cannot be
displayed unless we manually fix the row. Any suggestions?

 

[Guest]

Set the cells to wrap as normal and place the code below into the appropriate
sheet tab of the VB Editor:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireRow.AutoFit
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

Do you have any merged cells in the row?

Rows with Merged cells will not Autofit.

You need VBA code to do that.

Here is code from Greg Wilson.

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


Gord Dibben MS Excel MVP

Is there a way to override a manual row height setting, if you want word wrap
to work automatically in Excel?

We are setting up a template for users in our organisation to use, and want
this to happen automatically (ie. we don't want staff to have to adjust row
height themselves).

We have selected the rows (whose height had been previously altered
manually) and we chose Format Cells Alignment - Word Wrap on. We've also
chosen the Format, Row, AutoFit command. However, all the text cannot be
displayed unless we manually fix the row. Any suggestions?

Gord Dibben MS Excel MVP

 

[Guest]

Thank you Gord,

This fixed the problem ... I appreciate the assistance.

The template contained merged cells (which I neglected to notice or mention
in my first post - sorry MarkN - but I appreciate the input!).

Cheers
JD2

 

[Guest]

Dear Gord,

Further to your advice, we used the following code in a macro to
automatically resize rows:

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

Although we thought it worked a treat at first, further use reveals that if
we change a cell in column B to something shorter, it resizes to fit that
cell (eg. to a row height for one row of text), but if column A has two rows
worth of text, it hides one of those rows. You then have to manually click
in the cell in column A and then out again to get it to automatically fix it.
As this is an organisational template, we can envisage a lot of people
finding this frustrating. Do you have any suggestions on how we could
overcome this issue by adjusting our code?

Any assistance would be gratefully received.

Regards
JD2

 

[Gord Dibben]

Not much of a coder so having a hard time complying with your request.

Perhaps Greg or Jim Rech, who has written similar code, can jump in and bail me
out.

Why do you feel compelled to use merged cells in the first place?

These cause no end of problems with many Excel functions like copying, pasting,
sorting, filtering and as you have found, autofitting.


Gord

Dear Gord,

Further to your advice, we used the following code in a macro to
automatically resize rows:

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

Although we thought it worked a treat at first, further use reveals that if
we change a cell in column B to something shorter, it resizes to fit that
cell (eg. to a row height for one row of text), but if column A has two rows
worth of text, it hides one of those rows. You then have to manually click
in the cell in column A and then out again to get it to automatically fix it.
As this is an organisational template, we can envisage a lot of people
finding this frustrating. Do you have any suggestions on how we could
overcome this issue by adjusting our code?

Any assistance would be gratefully received.

Regards
JD2

Gord Dibben MS Excel MVP

 

[Guest]

Dear Gord,

Thank you for your prompt response - I appreciate your time and effort.

I did not design the workbook, but have been asked to fix the issue (and I'm
certainly not much of a coder either!). The workbook in question has several
sheets and a rather complex layout as it collects Key Performance Indicator
statistics and comments, etc. from each of our 35 business units. Thus the
merged cells have been used for displaying different quantities of
information in a pleasing way (personally I avoid them like the plague too!).


Gord, I have posted a question to the general audience again, so hopefully
one of the people you mentioned will respond to my cry for help.

Thank you again
Regards
JD2

 

[Debm]

I have a spreadsheet that is full of formulas pulling data from another
spreadsheet. The amount of text that ends up in the cells of the spreadsheet
with the formulas varies everytime. I tried using the code but it did not
work. I don't have any merged cells in either spreadsheet. Should the code
be different if there are no merged cells? Also all I did was View Code and
pasted in the code and then Alt Q to return to spreadsheet and then saved it.
Did I miss a step?

thx

 

[Debm]

Also I am using Excel 2003

I have a spreadsheet that is full of formulas pulling data from another
spreadsheet. The amount of text that ends up in the cells of the spreadsheet
with the formulas varies everytime. I tried using the code but it did not
work. I don't have any merged cells in either spreadsheet. Should the code
be different if there are no merged cells? Also all I did was View Code and
pasted in the code and then Alt Q to return to spreadsheet and then saved it.
Did I miss a step?

thx

 

[Gord Dibben]

The code only operates if cells are merged so no point using it.

I don't know if Row>Autofit will react to a change in quantity of data when
the results of a formula.

I can't get it work.

This seems to do the job when calculation takes place.

Private Sub Worksheet_Calculate()
Me.Rows.AutoFit
End Sub


Gord

 

[Debm]

Hi Gord: I was sure hoping you would respond. You are the best. This
worked perfectly and I appreciate it very very much!!!!

 

[data21117]

Hi Gord: I was sure hoping you would respond. You are the best. This worked perfectly and I appreciate it very very much!!!!"Gord Dibben" wrote:> The code only operates if cells are merged so no point using it. > > I don't know if Row>Autofit will react to a change in quantity of data when > the results of a formula.> > I can't get it work. > > This seems to do the job when calculation takes place. > > Private Sub Worksheet_Calculate()> Me.Rows..AutoFit > End Sub> > > Gord> > On Wed, 29 Jul 2009 10:54:03 -0700, Debm <[email protected]> > wrote:> > >I have a spreadsheet that is full of formulas pulling data from another > >spreadsheet. The amount of textthat ends up in the cells of the spreadsheet > >with the formulas varies everytime. I tried using the code but it did not > >work. I don't have any merged cells in either spreadsheet. Should the code > >be different if thereare no merged cells? Also all I did was View Code and > >pasted in the code and then Alt Q to return to spreadsheet and then saved it. > > Did I missa step?> >> >thx> > > >> >"Gord Dibben" wrote:> > > >> Do you have any merged cells in the row?> >> > >> Rows with Merged cells will not Autofit.> >>> >> You need VBA code to do that.> >> > >> Here is code from Greg Wilson.> >> > >> 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 > >> EndIf > >> End With > >> End Sub> >> > >> > >> Gord Dibben MS Excel MVP> >> >>> On Thu, 24 Aug 2006 19:44:01 -0700, JD2 <[email protected]>wrote: > >> > >> >Is there a way to override a manual row height setting, if you want word wrap > >> >to work automatically in Excel?> >> > > >> >We are setting up a template for users in our organisation to use, and want > >> >this to happen automatically (ie. we don't want staff to have to adjustrow > >> >height themselves).> >> > > >> >We have selected the rows (whoseheight had been previously altered > >> >manually) and we chose Format Cells Alignment - Word Wrap on. We've also > >> >chosen the Format, Row, AutoFit command. However, all the text cannot be > >> >displayed unless we manually fix the row. Any suggestions? > >> > >> Gord Dibben MS Excel MVP> >> > >

For what it is worth...
The code works for Excel 2007 if a cell is opened for editing [F2] and the enter key is pressed to close the cell.
Thank you all for posting these instructions.