+ Reply to Thread
Results 1 to 12 of 12

Auto Row Height code

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    42

    Auto Row Height code

    I'm grateful for the following code a member supplied to help me string together multiple cells with unique formatting into one cell while retaining the precise source cell formatting (including spacing), but when I use it the target cell (A11) will not autofit the row height. I could adjust the row manually, but it is a part of a series of called macros. Can anyone suggest code to insert within my macro?

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Auto Row Height code

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Auto Row Height code

    Try this (not tested)...

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Auto Row Height code

    Well, I gave it try and the AutoFit didn't work. The Cell didn't change size to accommodate the expanded text. Any other thoughts?

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Auto Row Height code

    Quote Originally Posted by Scott Taylor View Post
    Well, I gave it try and the AutoFit didn't work. The Cell didn't change size to accommodate the expanded text. Any other thoughts?
    Maybe try this.
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Auto Row Height code

    If you can attach a sample of what you're actually working with, it would help. The above code works for me, but I'm just using random formatting for test.

  7. #7
    Registered User
    Join Date
    07-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Auto Row Height code

    Here's the master workbook. The "Release" Sheet is where I am trying to populate the cell A11 with the information. I've only entered a small portion of the data and conditional formatting at this point.

    The code I'm trying to modify is as follows:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Auto Row Height code

    Hmm, all my tests seem to indicate that autofit just flat out doesn't work in this situation. Autofit works if the wrap text is on and the cells are unmerged, but the combination of wrapped text and merged cells seems to defeat the autofit, regardless of what order I apply the wrapping and merging in (through vba OR the GUI). I think you're going to have to go with a manual height, which does work. If the height is going to be variable, you can probably fake the autofit with some math in your vba, but, in my experience, it's incredibly difficult to make that work consistently in Excel.

  9. #9
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Auto Row Height code

    Some very basic testing would seem to indicate that you may be able to use a macro to Insert an Word Doc Object into the spread sheet, set the width you desire, then paste your formatted text into the Object. I don't have time to go down that road right now, but I think it will work, since it works when I do it manually.

  10. #10
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Auto Row Height code

    This is a bit of a kludge but it works...

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Auto Row Height code

    Sounds like an intriguing proposal. I'm going to try that and see where it takes me. Thanks.

  12. #12
    Registered User
    Join Date
    07-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Auto Row Height code

    Alpha! it worked.....Thanks so much. I need to have the cell fully justified as well. I started it out as justified, but after running the macro it is aligned to the left.

    This has been quite a learning curve for me...thanks to all who have contributed!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1