Free Microsoft Excel 2013 Quick Reference

Word Wrap/Auto Fit Macro

I have a form with checkboxes wherein when the checkbox is True it unhides 3 rows which the user needs to fill in. There are NO merged cells involved, word wrap is on and autofit row is used, and it is not too many characters, however, it doesn't consistently increase the row height as needed. I'm in 2010 but am programming for 2003.
The rows that need to grow/shrink are part of range Group01 and it's A9:E11 (there are 20 boxes with rows to unhide and fill in).
Is there a method to say if E9 is modified activate row auto fit or something?
Any suggestions to get it to automatically fit the row or to use code to fit it?
We'd really like it to be auto-fit like it should without the user having to deal with it.

Post your answer or comment

comments powered by Disqus
Hello to everyone,

After looking through the board archive I was unable to find a solution so I thought I would post instead.

I have a cell that I simply want to have display the contents of another cell earlier in the sheet (for eg. A1).

The formula I have is simply:


This works fine, although sometimes the contents of A1 will be long so I want the cell in question to word wrap, and I have also set the cell's row to Auto-fit.

Then I have to protect the document (only unlocked cells selectable). A1 is unlocked, the cell in question is locked.

However when I enter a long string into A1 the cell in question does not wordwrap and so most of the entry is not seen. However, if I then unprotect the document and do anything to the cell in question it instantly word wraps fine.

Perhaps the problem lies with protecting the document? This is essential though.

Apologies if this is a long post for a simple question but I wanted to be thorough.


I followed the directions on another post in the forum, but it didn't seem to solve the first user's problem either. (I am using Excel 2000)

I am making notes on a PPT presentation, with a cell for each slide. I am using column B for my notes (col A is just an auto-fill series of numbers, one for each slide). I am entering my notes in column B, then for the next slide I use the cell below it. Some slides have a lot of notes of several lines.

I have word-wrap on all the cells in the worksheet.

I have tried clicking on the lines between rows to auto-size the row height. It will correctly auto-size some cells (it will shrink to fit to smaller heights correctly). It will also increase the row height to fit some cells correctly... but ONLY to a certain level. For larger cells, it won't increase the row height more than 165.75 for some reason. Even if I manually change the height to a height where I can see everything, if I select all and auto-fit again, it will shrink those all back down to 165.75!!

Whyyyy? ; )

Thank you!

OH! Also... for some reason it will not word-wrap some of my cells. I am pasting into there from notepad, and have tried both turning notepad on or off to see if that makes a difference before pasting (yes, I have the wrap-text on for the excel cell also)

very confusing =(

Dear All,

I have inserted the a lot picture in Table Rows. I want they AUTO fit to column width by one macro code or word 2010 tricks.

Please advise perfect solution.



At the end of my macro i put a large number in a specific cell (the cell changes each time the program is run), so i would like to auto-fit the column so that this number is visible (instead of seeing ### in the cell).

I've tried a few variations of the following, but it doesn't seem to work:

Column = ActiveCell.Column

Any assistance would be appreciated.


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
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
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

Often when I set cells to word-wrap and then auto-fit the row height, cells
with text will end up with a blank line or two at the top. It's not extra
spaces in the text because when I go to the formula bar, there's nothing
before the start of the text. I can manually adjust the size of each row,
but that's a hassle and not very consistent!


The merged cells do not auto row height properly and therefore do do word
wrap. If you manually size the row, it will word wrap properly.

Using Excel 2003 if you have a number of cells that are merged and wrapped
how can you get the rows to auto fit height - I am sure this used to work on
previous versions of excel but does not seem to now (similarly for columns).
Is there a fix for this or an option I need to select or deselect?

There are two or three commands that I use ALL the time that don't have button images (maybe they do in v. 2003, but I don't know).

One is the Define Name command for working with named ranges and constants. Another is Conditional Formatting.

Anyway, I created a page on my site for listing a few button images that you can download, along with how to add them to existing command buttons. If anyone has any really cool button images, I'd be more than happy to list them (and include your name if you want).

Excel Toolbar Button Images

By the way, I finally figured out how to preserve the transparency in GIF images. The trick is that you have to insert the picture into Excel first. Then copy it. Then paste the image. I listed the steps on the page so that I don't forget later.

I got a little carried away, and added a couple of other macros, such as a button (and macro) for Word Wrap and a Cell Format UserForm. Instead of creating an add-in for all this, I wrote up instructions for creating your own add-in. It's all pretty basic stuff.

If you like to add labels with a lot of subscripts, superscripts, symbol fonts, etc. like I do, then you might find the UserForm pretty fun. It was a long time ago that I created it (back when I was first learning how to create UserForms), so I'm not very sure where the original idea for such a form came from. If anyone knows, I'd like to provide a reference.


Hi there,

I have a paragraph of text I want to insert into the body of an email. No problem at all with the code and my macro works. Due to the length of the paragraph of text, however, I would like to word wrap it within the VBA editor for ease of reading/editing.

Based on a search, it would seem the solution to this has been posted many times to many forums. Looks like you just put a _ at the end of a line and start a new line, but I'm obviously missing something simple, as that doesn't work for me.

Would someone please take pity on this poor sod and show him how to word wrap a .Body "yadayadayada" line in VBA editor?

I would also like to create a line break within the body of text in the email - so I can write a second paragraph - as well as insert a couple of hyperlinks.

Thanks very much,

I do not seem to be able to consistently control word wrapping in the Category Axis area in the series of charts I create quarterly. The individual names appearing are basically the same, but sometimes they word wrap and sometime they don't. In the attached sample, sheet C2-Location: PPO chart is just the way it needs to be - NO WORD WRAP. But if you change that chart to another location it word wraps. (To change locations, go to sheet D2 which controls sheet C2 chart, and change the location to SGO.) It appears that the less bars in the chart the greater the problem.

I have spent 2 1/2 hrs trying to get in control of this. I have changed font sizes, changed the plot area, changed the grid lines scale, changed the cell size on the originating sheet (D2), removed the check mark in Auto Scale on the Font tab, and stretched/reduced the entire chart without consistent success. What is most frustrating is that twice I have "accidently" gotten a chart to all of a sudden not word wrap but don't know why because when I try to repeat what I think I did before, it does not work!

Additionally, at times it may appear correct on the screen, but when I do Print Preview, it is word wrapped! I have searched multiple sites and googled multiple Excel solutions as well as pulled out some books but cannot find any references regarding how to control this.

Please help. Kathryn

Dear Expert,

I would like to make the height of the row autofit to the contents of the data that I typed in.

For example, when I typed in a long sentence (8 lines) in a cell, I would like it to auto fit the height of the row. I have 'wrap text' of the cell. What I got was the first line of the text and I have to add the height of the row in order to show the subsequent lines. Can it be autofit automatically when it goes to line more than 1?

Kindly refer to attachment file.

Please help.

I have cells A1:H1 merged into one cell and set to wrap text. In this cell I need to type a text message, sometimes the message will fit in these merged cells and sometimes it won't depending on the size of the message. I would like to expand row 1 automatically, if needed to accommodate the size of the message. I tried auto fit and can't seem to get it to work.
Thanks Trevor2002

I can get the test to wrap around and the cell adjust to size when working
with a single cell but not when the cells are merged together - how to you
get merged cells to auto fit when the text in the merged cell wraps around to
the next line?


We are having difficulty making Excel 2000 use word wrap in a merged cell.
The cells are formatted as Text & set to word wrap before the merge.
(and are still set as above after the merge)
The row is set to Autoheight.
Once two cells are merged into one, this autoheight feature no longer works.
The text is truncated indicating that it is wrapping to the next line below
but I cannot see the text on the second line.

Word wrap works as expected in an unmerged cell. That is, the row height
automatically adjusts to fit the multiple lines of text without inserting
hard returns (Alt+Enter).

I cannot double-click on the row seperator to have it automatically resize
the row height either. My only resolution is to drag the row seperator until
i like what i see.

Ideally I would prefer to find a setting in the options dialog that would
re-enable this autoheight feature on merged cells.

Does anyone know if this is possible?

Partners Healthcare

Good afternoon.

I have a form with protection and validation in several cells; some are left
unlocked/no validation to allow for free form text entries. My problem is
that when i protect the sheet, Excel doesn't auto fit the text in the
column/row and the user is not able to either. (I don't want to allow Format
row height / column width in Protection options, as I don't want any hidden
rows/columns unhidden, which these protection options allow)

Is there a way to allow the user to modify the row height and column with of
the unlocked text cells (as needed) while the sheet remains protected?

Or a way for Excel to do this automatically? I have wrap text selected in
the cell format options.

Thanks! -- Suzanne.

I have rows (B20:G20) to (B24:G24) merged. I want to type text in B20 and when it gets to the end of G20 to automatically flow into B21; and when it gets to G21 to automatically flow into row (B22:G22) and so on.

I can press enter at the end of lets say G20 and go to B21 but I have to be careful to limit the end of the text so it fits in B20:G20 so I want to automate this.

Basically I want a word wrap to the next row at the end of cell column G.

I currently have a group of cells merged together where I am using the Concatenate formula. The worksheet is not edited by the user only printed. The problem is this cell has the capability of being 1 line or eight lines long. Since the cells are merged the autofit format and the word wrap do not automatically adjust the row height.

I need a formula or macro that is completely self contained, where the user does nothing to activate, where it will appear to be automatic from the users view point. This sheet will be protected where nothing can be edited by the user only printed. I am fairly proficient when it comes to formulas but macros I am a beginner.

any help that you can provide will be greatly appreciated


I have a text cell in an Excel spreadsheet that I would like to the cell to auto fit to the size of the text (not wrap). Is this possible? If so, how?


I have text in cells that has been wrapped, but for some reason auto-fit does not work. When I choose auto-fit, it takes the row height back to the original height, leaving much of the text unseen. Does anyone know why this occurs? Sometimes I have data in the same column using the same formats, and those rows will allow auto-fit. Any advice or solutions?

There are times when I attempt to resize the row height to fit the contents
of the cell but the auto fit does not do anything. I have checked to make
sure the wrap test box is checked and it is, but the row does not get any
bigger. Also, the wrapped portion of the text does not show in the cell but
does appear in the formula box. Any ideas?

I have Microsoft 2003 installed on my computer. In excel I have turned wrap
text in the format font options. I have also turned AutoFit on the Format Row
options, yet when I write a line of text longer than the cell width it
doesn't resize the cell for me.

The wrap text option is working correcty. The Auto-Fit isn't.

Any suggestions?

Good afternoon Excel Forum

I have created a worksheet that allows users to enter text into certain unlocked cells, these cells are merges with text wrap on.
When you first select the sheet and type in the cell, they auto fit as required
The sheet is then copied via a control button which makes a copy of the sheets and allows auto renaming of the tab. This all works fine.
The problem comes when you go back to the master sheet, when you enter the text into the cells as before it no longer auto fits unless you double click the cell.

Why does this only work once, why does it stop working when a copy of the master has been made?
I have enclosed the code for the auti fit which i use

I would like the sheet to continually autofit every time the sheet is used.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Auto adjust rows to fit text entered

Sheets("RA Sheet").Select
End Sub
Thank you in advance.

I am Importing a text file with my macro, and need to remove the word
wrap before Importing. Can I do this in the macro itself? Right now I
am doing this manually before I run the macro.

No luck finding an answer? You could always try Google.