Excel 2003

[Deb]

I have formatted my cells to auto-fit and wrap. There are no merged cells.
There are formulas in the cells that return text depending on the if
statement.

When the if statement returns "true" and picks up the text, it doesn't wrap
in the cell. It ends up being a long string.

 

[Gary''s Student]

I don't see what you are seeing. On my computer, the wrap is there but the
autofit is NOT automatic.

 

[Deb]

My worksheet has the rows formatted to auto-fit; i didn't specify a certain
height. I then formatted the relevant cells with "wrap text" checked on the
Alignment tab.

In these cells i have an "if" formula to pick up relevant text. When this
formula returns text, the text doesn't wrap unless i manually uncheck wrap
text and then go back in and check it again. I have a lot of cells that need
to do this and to manually do this with each cell is very time consuming. Is
there a way to get this to work that is not manual.

 

[Gary''s Student]

Here is an example for a single cell, A2.
The cell contains a formula that return text of various lengths and we want
Excel to re-adjust the row height whenever the formula is calculated.

In the worksheet code area, insert the following event macro:

Private Sub Worksheet_Calculate()
Set r = Range("A2")
Application.EnableEvents = False
r.WrapText = False
DoEvents
r.WrapText = True
DoEvents
Application.EnableEvents = True
End Sub

This is only a single cell demo. For your case it would need to be expanded
for all the cells you want to adjust.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm