Word wrapping in Excel

3378 views
Skip to first unread message

JD2

unread,
Aug 24, 2006, 10:44:01 PM8/24/06
to
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?

MarkN

unread,
Aug 25, 2006, 12:55:02 AM8/25/06
to
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

--
Hope this helps,
MarkN

Gord Dibben

unread,
Aug 25, 2006, 11:52:49 AM8/25/06
to
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

Gord Dibben MS Excel MVP

JD2

unread,
Sep 6, 2006, 3:45:02 AM9/6/06
to
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

JD2

unread,
Oct 31, 2006, 11:24:02 PM10/31/06
to
Dear Gord,

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

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

unread,
Nov 1, 2006, 12:13:09 PM11/1/06
to
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

JD2

unread,
Nov 1, 2006, 4:48:02 PM11/1/06
to
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

Debm

unread,
Jul 29, 2009, 1:54:03 PM7/29/09
to
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

unread,
Jul 29, 2009, 3:07:01 PM7/29/09
to
Also I am using Excel 2003

Gord Dibben

unread,
Jul 29, 2009, 6:38:34 PM7/29/09
to
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 <De...@discussions.microsoft.com>
wrote:

Debm

unread,
Jul 30, 2009, 11:46:01 AM7/30/09
to
Hi Gord: I was sure hoping you would respond. You are the best. This
worked perfectly and I appreciate it very very much!!!!

data...@gmail.com

unread,
Oct 15, 2012, 11:45:49 AM10/15/12
to
On Thursday, July 30, 2009 11:46:01 AM UTC-4, Debm wrote:
> 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 <De...@discussions.microsoft.com> > wrote:> > >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" 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 > >> End If > >> End With > >> End Sub> >> > >> > >> Gord Dibben MS Excel MVP> >> > >> On Thu, 24 Aug 2006 19:44:01 -0700, JD2 <J...@discussions.microsoft.com> 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 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> >> > >

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.
Reply all
Reply to author
Forward
0 new messages