Automatic Row Height

bomalley

New Member
Joined
Feb 16, 2002
Messages
21
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).
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Howdy,

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

Hope so. Cheers, Nate
 
Upvote 0
I can do that, but if the cell data changes, it doesn't automatically shrink or expand the row height accordingly.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top