Automatic Row Height

[bomalley]

Hi there,

Does anyone know how to make row height automatic after changing values in cells.
I have data in a cell that automatically word wraps, and increases the height of the row. However if that data changes, the row height stays the same, rather than shrinking (if the length of the data is less).

[NateO]

Howdy,

Does it help if you select format cell -> alignment -> wrap text?

Hope so. Cheers, Nate

[bomalley]

I can do that, but if the cell data changes, it doesn't automatically shrink or expand the row height accordingly.

[NateO]

It does for me, XL 2000....

[bomalley]

Using Excel 97 (at work)

[Jack in the UK]

Hi guys

Im sure i have had this before i use 97 at work and 2000 or XP(2002) at home and 97 fails this test higher levels of XL will

HTH
Rdgs
==========
Jack

PS so if im right you will be on 97 i hope!

Sorry a fix. ui have this every day and i ignor it and ever so often coffe btraek i select all and double click a line limit on the left side that the slider to the row adjustment and its junps and re jiggs to fit nicley.

Crude but work for me..
HTH

[bomalley]

Yep - I do that too - I was just hopeful of a way I could make it automatic

[NateO]

Bo,

Right click on the worksheet in question. Click on 'View Code.'

Paste:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1:f100")) Is Nothing Then
Rows("1:100").AutoFit
End If
End Sub


Change the ranges so that they're appropriate. Close the vb editor and give it a shot.

Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-19 17:38 ]

[bomalley]

You are a god Nate. This works fantastically. One more question though, can I change it so that that macro runs on a change to a drop down menu control I have on the sheet? I don't know VBA very well, so am not sure of the code changes I would need to make.

[NateO]

Bo, thanks! I'm now at a terminal without Excel, so I'm not testing this, but try the following (same process, right click on the sheet):

1) If you want a macro to run, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:f100")) Is Nothing Then
application.run("Bo's Macro Right Here")
End If
End Sub

2) If you want the user to be shot to a cell with data validation try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:f100")) Is Nothing Then
range("a1").select
End If
End Sub

Again, change the cell ranges as appropriate. And if it's a macro (scenario 1), write the macro name without 'sub.' You can slide the autofit line in this too (within the 'if' statement) if it works as anticipated.

Hope this helps.

Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-19 18:51 ]