Non-VBA to gow grow based on another cell?

[Lostguy]

Hello,

If I type in dog;cat;fish;cow;rat in cell A1, what I want in cell B1
is:

dog
cat
fish
cow
rat

stacked on top of each other.

I used =substitute(a1,";",char(10)) and checking "word wrap", so now
it looks like it is supposed to.

But when I add more entries in cell A1, I don't know of a way to get
the cell to expand row height so the entries don't scroll off. And
when I reduce entries down to three, the cell should shrink.

Basically, the height of the row should match the contents.

Can this be done non-VBA?

Thanks for all your help with this and past questions!

VR/

Lost

 

[Gary''s Student]

An excellent question!

Try the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set A1 = Range("A1")
If Intersect(A1, Target) Is Nothing Then Exit Sub
A1.Rows.AutoFit
End Sub


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

 

[Lostguy]

Gary's student,

It looks like that will work, but I am looking for a method (if there
is one) that does not require VB or macros.

I have many users that select No when that message pops up about
macros may cause viruses. Too many users, in fact.

So everything in the workbook has to either be something you can do
via a formula or in the setup options somehow.

Sorry about causing that confusion.

VR/

Lost

 

[Ron Rosenfeld]

Hello,

If I type in dog;cat;fish;cow;rat in cell A1, what I want in cell B1
is:

dog
cat
fish
cow
rat

stacked on top of each other.

I used =substitute(a1,";",char(10)) and checking "word wrap", so now
it looks like it is supposed to.

But when I add more entries in cell A1, I don't know of a way to get
the cell to expand row height so the entries don't scroll off. And
when I reduce entries down to three, the cell should shrink.

Basically, the height of the row should match the contents.

Can this be done non-VBA?

So far as I know, your users would have to resize the cell manually without
using VBA. They could use the Format/Row height AutoFit option, or do it with
the mouse.
--ron