Macro to Resize Cells for Word Wrap

  • Thread starter Thread starter Randy Numbers
  • Start date Start date
R

Randy Numbers

Hi folks,

Would like to write a macro that checks the cells in column D, and resizes
those that have wrap-around text that is not showing.

Many thanks... ./Randy
 
Randy,

What do you want to change to resize? Column width? Height? For height,
you can set all the rows to autofit (Format - Rows - Autofit). Rows that
have been manually height-adjusted go out of autofit mode. You can set all
of column D to wrap by selecting it, then Format - Cells - Alignment - Word
wrap. Maybe this will take care of this situation.
 
Earl,

Thanks for your reply -- sory foir not being more clear: my speradsheet has
certain cells for free-form text. Sometimes they enter more text than gets
displayed; other times there is much empty space. I have protected the
sheets so manual resizing is not possible for the user. I would like to
create a macro that:
1. Unlocks the sheet
2. Resizes the cells to show full text (adjusting the height)
3. Relock the sheet

I realize the lock/unlock exposes the password -- and that's ok.

Thanks! .../Rn
 
ActiveSheet.Unprotect Password:="MyPass"
Cells.Rows.AutoFit
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:="MyPass"
 
You may wish to experiment with a relatively long text string (2000
characters or so ; using font size 8) entered into a worksheet cell of
50-wide.

In this case, Autofit does not help ; Spelling checker does not help ;
Find and Replace do not help neither.

I'm sure that MS can do something about it very easily.

Only dodgers would quip : why would one deal with long text in Excel ?

Similar queries have emerged time and again in this neighbourhood ;
search the archive. Is life meant to be easy, or what ?

Regards.
 
Earl,

This does the trick -- although I noticed it won't work on merged cells
(neither will the command from the menu...) . Thanks RN

BTW, can you recommend a good book that VBA -- I bought John Green's Excel
2000 VBA Programmers Guide and it is either too advnaced or not laid out
well for a me.
 
For excel books, Debra Dalgleish has a big list of books at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. I think that John Green
(and others) is nice, too. See if you can find them in your local bookstore and
you can choose what
one you like best.
 
Randy,

Autofit doesn't seem to work manually. Yet ANOTHER reason to avoid merged
cells, it would seem. The list has gotten so long I can't remember them
all. Kind of like the foolish things Cox Communications has done in our
area over the years. Can't remember them all.
 
Back
Top