Automatic Row Height
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Automatic Row Height

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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).

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Howdy,

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

    Hope so. Cheers, Nate

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It does for me, XL 2000....

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Using Excel 97 (at work)

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com