Row Expansion

S

Susan

Is there anyway to set a row to automatically expand when info is transferred
to it from another sheet?
 
O

Otto Moehrbach

If you set the destination cell to Wrap, the cell will automatically
increase/decrease in height to accommodate the quantity of text in the cell.
Is that what you wanted?
If you want the column width to change automatically, you will need an event
macro. Please post back if you need more. HTH Otto
 
S

Susan

I checked my cells formatting and the text wrapping option is selected but
the row does not increase in height when text is transferred. If I do a
column expansion, I'm afraid my spreadsheet will exceed my margins. Is there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer
 
O

Otto Moehrbach

Susan
Your right. When you copy data from a cell and paste it into a cell
that is formatted for Wrap, the format of the source cell comes along with
the paste and you lose the Wrap formatting. You can manually set the format
of that cell back to Wrap or you can run the following macro with that cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into which you
will be pasting the data. Something like "any cell in Column A" or "any
cell in Column A from this row to that row". Or any other column or groups
of columns and groups of rows. If you can nail down the range into which
you will be pasting, then when you paste into any cell in that range, the
format will be changed to Wrap. Post back if you think something like this
will help you. HTH Otto
 
S

Susan

Otto,

The source cell from where I am transferring data is already set to word
wrap. I need a macro that when the data is transferred to the destination
cell the row that it is being transferred to will increase in height
automatically to accomodate viewing the entire cell without having to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer
 
O

Otto Moehrbach

Susan
I don't know what you have in your file for it to act this way. If the
source cell is formatted for Wrap and you copy it and paste it into any
cell, that cell (the destination cell) will be formatted for Wrap regardless
of its format before the paste. You say that is not happening for you.
Yes, there is a way to automatically format a cell immediately after you
paste into it. You would need a Worksheet_Change event macro. Such a macro
fires (executes) whenever the content of any cell in the entire sheet
changes. You would need to write the code of the macro to take the action
you want (format to wrap) whenever any cell within your range changes. I'm
sure you don't want this macro to take the Wrap action when any cell in the
entire sheet changes. Can you specify the range in which you want this
action to occur? Like "Anywhere in Column A from row 3 to row 150". Or
"Anywhere in Columns C:K from rows 23 to 432. Or etc. Whatever range you
come up with, you need to name it. Say "TheRange". The following macro
will change the format of the cell to Wrap when the changed cell is within
TheRange.
Sub SetWrap()
If Target="" Then Exit Sub
If Intersect(Target, Range("TheRange")) Then _
Target.WrapText = True
End Sub

Note that such a macro must NOT be placed in a standard module. It MUST be
placed in the sheet module for the sheet in which you want this to happen.
To access that module, right-click on the sheet tab of your sheet. From the
menu that pops up select View Code. Paste this macro into that module.
Click on the "X" in the top right corner of the module to return to your
sheet. Please post back if you need more. HTH Otto
 
S

Susan

Otto,

I am trying to enter the macro from your message below, but I'm getting an
error message with this line of the macro: If Intersect(Target, Range
(Range_1))_Then. I named my range Range_1.

Susan
 
S

Susan

Otto,

I input the macro as follows:

Sub SetWrap()
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub

My rows are still not widening when the info is transferred. I looked back
at your directions and noticed that you referred to pasting. The data that I
am transferring to the destination cell is getting there from code inserted
into visual basic. Would that affect the macro you are giving me from
working?

Susan
 
O

Otto Moehrbach

Susan
I messed you up. I told you I was giving you an event macro then I gave
you standard macro.
Here is the macro you should be using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub
And this will work regardless of who puts data into the Range_1 range, you
or the code. Sorry for the mix-up. Otto
 
S

Susan

Otto,

The macro didn't work again. I typed it in just as you had it, but no luck.
I think I'm going to give up on this. Thank you for your help and patience.

Susan Spencer
 
O

Otto Moehrbach

Susan
Don't give up. This stuff is too easy to just give up on it. What you
want to do is very common and is done by lots of people virtually all the
time. If you wish, send me your file or a sample of it. Fake the actual
data if you feel it is proprietary. I want just the layout. I don't need
your code that puts data into one of the Range_1 cells if you don't want to
send it. I can write that myself.
My email address is [email protected]. Remove the "nop" from this
address. Otto
 
Top