Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Merged cells / Row height (2000)

    I have text in merged cells with word wrap, but the row height will not automatically adjust when more text is added.

    Is there a way to have the row height automatically adjust for merged cells?

    Thank you.

    -tmg9671 <img src=/S/please.gif border=0 alt=please width=31 height=23>

  2. #2
    Star Lounger
    Join Date
    Jan 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    54
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Merged cells / Row height (2000)

    I'm pretty sure there isn't. I've just tried various combinations of formatting and height features, and it seems that merged cells can't do automatic height changes for word wrap. You can manually adjust the height of a row with merged cells (you've maybe tried that), but I don't think you can get row height to automatically adjust for merged cells.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merged cells / Row height (2000)

    Check out <post#=270923>post 270923</post#> for some code as a workaround.

    Steve

  4. #4
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Merged cells / Row height (2000)

    Thank you for the instructions, but I still cannot get it to work, manually or automatically. I followed all the directions but it still will not work. Does it matter which module the code is located in? Please advise. Thank you.

    -tmg9671

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merged cells / Row height (2000)

    The AutofitMergedCellRowHeight from the website link, goes in a standard module.

    The "Worksheet_Change" code that I provided in the post, does not go into a module. It goes into the worksheet object of the sheet with the merged cells. It works on the cell A1 you will have to adjust the range as appropriate to run whenever those cells change.

    Steve

  6. #6
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Merged cells / Row height (2000)

    I'm sorry. It still doesn't work.

    Does it matter which module the AutoFitMergedCellRowHeight code is in? I have four of them in the VBA.

    -tmg9671

    (I am really starting to feel like a doofus!!)

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merged cells / Row height (2000)

    It can go into any module.

    Some questions:
    1) do you have "wrap text" selected in the cell(s) of interest? [It won't expand if you don't wrap, just like the autofit]
    2) if you have cell that is merged (and is marked to "wrap text") and you select it and run the macro, does it work? [then the macro works fine]
    3) If 1 & 2 are okay the problem is with the worksheet change macro.
    What cell(s) are you trying to autowrap?
    What sheet are they in?
    Where do you have the "change" macro?
    What modifications did you make to it (as I mentioned earlier, I posted only an example that was set assuming ONLY cell A1 was to be adjusted, it needs to be adjusted to change whatever cells you are interested in)

    Steve

  8. #8
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Merged cells / Row height (2000)

    Okay...

    1. "wrap text" is selected
    2. yes. the macro works fine if I manually select the cell and manually run the macro.
    3. >the cell that changes is B25. I changed the code to reflect that. (Note: B25 is B25:V25 merged)
    >they are in a sheet called "Contract"
    >the "change" macro is in sheet 1 (Contract)
    >the only modifications are to cell B25

    -tmg9671

    I think we are almost there...

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merged cells / Row height (2000)

    Did you change the macro to look for changes in B25 as below?

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("B25")) Is Nothing Then
    Target.Select
    AutoFitMergedCellRowHeight
    End If
    End Sub</pre>


    Steve

  10. #10
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Merged cells / Row height (2000)

    I found the problem...

    The data in cell B25 is actually read from a refreshed query table. ie, I am using the command "=Query!D2" where Query is the sheet with the refreshed external data and D2 is the data I need to go into cell B25 on the Contract sheet.

    The macro works automatically if I enter data manually into the cell B25, but when it reads from Query!D2, it doesn't work.

    I tried putting the query in the contract sheet so that the data appears beginning in cell AC25 (same row but off the printed page) in order to get the refreshed data to force the line height automatically, but now I have the problem where the column width is fixed whenever the data is refreshed.

    I know that I am rambling on, but this is the best way that I can describe it. You have helped me tremendously, and I believe that I am 98% there...if I can just get past the above glitches.

    As always, thank you for you help.

    -tmg9671

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merged cells / Row height (2000)

    The macro is triggered by the change in B25. If you don't change the contents of B25 the macro is not changed. Having a formula in B25 means that the contents will not change (the "value" changes), but not the contents of the cell ("=Query!D2"). This stays the same, not triggering a change.

    try something like this in the query sheet:
    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("d2")) Is Nothing Then
    worksheets("contract").activate
    Range("b25").Select
    AutoFitMergedCellRowHeight
    End If
    End Sub</pre>

    This should run whenever Query!D2 changes and then will change the row ht of contracts!B25

    or even just add the lines of code:
    <pre> worksheets("contract").activate
    Range("b25").Select
    AutoFitMergedCellRowHeight</pre>


    to a macro you might have that refreshes the data.
    If you don't use one, create a macro that refreshes the data then updates the cell contract!B25.


    Steve

  12. #12
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Merged cells / Row height (2000)

    Halleluja!!!!!!!

    Steve, thank you soooooooooo much for all of your help. I couldn't have done it without you.

    BRAVO TO STEVE!!!!!!!

    -tmg9671

    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  13. #13
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Merged cells / Row height (2000)

    Okay, I hit a glitch in this process...

    The process works perfectly if the data in the row is 32 lines or less. (The row height will only increase to 409.50)

    I tried merging two rows in order to increase the space, (B25:V26) but now the height of the two rows do not adjust (increase or decrease).

    How can the coding be adjusted to accomodate more that one merged row?

    -tmg9671

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merged cells / Row height (2000)

    RowHeight is limited to 409. This is an excel problem, not the macro problem.

    To do 2 (or more rows that are merged) change 3 lines:
    This line makes sure it only runs if you have 1 row merged:
    <pre>If .Rows.Count = 1 And .WrapText = True Then</pre>

    Change it to:
    <pre>If .WrapText = True Then</pre>


    This line sets the "CurrentRowHeight" variable to the mergeArea .RowHeight. This will give an error if the rows are merged (the "rowHeight = "Null") we will by pass it by just checking the activecell row height:
    From:
    <pre>CurrentRowHeight = .RowHeight</pre>

    To:
    <pre>CurrentRowHeight = ActiveCell.RowHeight</pre>

    And lastly for the same reason as above, Change from:
    <pre>PossNewRowHeight = .RowHeight</pre>

    To:
    <pre>PossNewRowHeight = ActiveCell.RowHeight</pre>


    You might have to change the "0.71" factor used for adjusting. I am not sure where it came from. I would guess it was trial and error. It might be necessary to adjust it based on the zoom which can affect the looks sometimes.

    I will warn you, before you rack your brain and then post again, the next problem you will encounter, since you seem to working with very large strings in the merged cell is that if you have over "approx 1024" characters to display (the exact number depend on many factors), it will not work. It will only set the row necessary to display the 1,024 characters, no more. Manually expanding will not display more. This is a "bug"/ "feature" in excel that you will have to live with. This is not a merged cell issue. You would have the same problem even without using merged cells.

    The only options are to use a textbox or to separate the whole string into substrings (by paragraphs?) into separate cells, then you could run the macro on each of the cells to adjust each one's row height



    Steve

Posting Permissions

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