+ Reply to Thread
Results 1 to 9 of 9

Code to merge rows to overcome row height max

  1. #1
    Registered User
    Join Date
    10-04-2017
    Location
    North Carolina
    MS-Off Ver
    2013
    Posts
    16

    Code to merge rows to overcome row height max

    Hello all,

    I have a worksheet in a large workbook that compiles results into one row (with cells A1:K1 pre-merged). The amount of text that is compiled will always vary, and will often exceed the max row height. Is there a way to have code detect the total height of the text and automatically merge the correct number of rows, while maxing out the height of those merged rows as needed, to display all of the text correctly? The end goal is one large completely visible cell that the user can copy the text from and paste into another program. Thank you for any help you can give!

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    3,998

    Re: Code to merge rows to overcome row height max

    1 Other than this one "monster" cell what else is included on this worksheet?
    2 Give some idea of the MAXIMUM number of characters anticipated in the cell?
    3 Does the cell contain actual text or a formula?
    4 Ignore this question if the cell contains a formula - how is the text compiled?
    5 Ignore this question if the cell contains a formula - do all characters in the cell have the same format?
    Last edited by kev_; 02-28-2018 at 01:46 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    3,998

    Re: Code to merge rows to overcome row height max

    Have you considered using a textbox
    - make it as wide as you require
    - set it to auto-size to fit text
    - height then determined automatically

    InsertTextBox.jpg

    TextBoxResize.jpg

    A text box can contain multiple formatting as illustrated below

    TextBoxMultiFormat.jpg
    Last edited by kev_; 02-28-2018 at 02:08 AM.

  4. #4
    Registered User
    Join Date
    10-04-2017
    Location
    North Carolina
    MS-Off Ver
    2013
    Posts
    16

    Re: Code to merge rows to overcome row height max

    The text box seems to be a great idea! But I'm having an issue populating it. I have another worksheet that compiles the results from the others using a series of concat functions. I created a macro to populate the text box by copying the cell with the final compilation from this worksheet and pasting into the text box. But the macro is coded with the contents of the cell at the moment I created the macro, the result being that the compilation cell will update, but the macro pastes only the original text rather than going to get the updated text at the time the macro is run. It seems to be some kind of limit on the copying/pasting into the text box. Below is the coding for the macro:

    Please Login or Register  to view this content.
    I have tried straight up pasting, pasting to keep source formatting, pasting text only, and the only part that changes is the "With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 281).Font" section. Any way around this issue, cause I really like the text box solution...

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    3,998

    Re: Code to merge rows to overcome row height max

    EDIT - oh - I did not see your last post - my screen did not update for some reason (probably my fault! )
    Hi

    The practicality of this suggestion depends on how complex is the cell formatting (lots more conditions?) and how many characters are in the box (time to run?)

    VBA to loop through each character in the cell and match the formatting
    - below is a hasty effort but you get the idea I am sure

    In attached file, run with {CTRL} k


    Please Login or Register  to view this content.
    EDIT - oh - I did not see your last post - my screen did not update for some reason (probably my fault! )
    Attached Files Attached Files
    Last edited by kev_; 02-28-2018 at 01:44 PM.

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    3,998

    Re: Code to merge rows to overcome row height max

    But the macro is coded with the contents of the cell at the moment I created the macro, the result being that the compilation cell will update, but the macro pastes only the original text rather than going to get the updated text at the time the macro is run.
    It seems to be some kind of limit on the copying/pasting into the text box.
    I must be misunderstanding your issue

    Both these behave as expected and populate the textbox with the required text:
    Please Login or Register  to view this content.
    Why does your macro (in post#4) limit formatting to the first 281 characters?
    - is that deliberate?

    If not, then this would format all characters:
    Please Login or Register  to view this content.

    Cumulatively adding text and formatting on the hoof
    If text is added to the textbox, formatting could be applied at the same time by applying it to the latest characters added - which could be easier if varying formats being applied in different sections.
    Last edited by kev_; 02-28-2018 at 01:43 PM.

  7. #7
    Registered User
    Join Date
    10-04-2017
    Location
    North Carolina
    MS-Off Ver
    2013
    Posts
    16

    Re: Code to merge rows to overcome row height max

    So I ended up creating a macro that adds a formula to the text box to insert the data from the compilation cell. The macro also deletes the formula so that the text now in the text box can be manipulated. This is what I ended up with:

    Please Login or Register  to view this content.
    I really appreciate your help, I would have spent who knows how many hours dealing with the row height max issue. The internet is amazing
    Last edited by jrooney7; 02-28-2018 at 10:20 PM.

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    3,998

    Re: Code to merge rows to overcome row height max

    You are welcome Thanks for the rep and for sharing your solution. To mark thread as solved see ThreadTools at top of thread

    The internet is amazing
    Yep!

  9. #9
    Registered User
    Join Date
    10-04-2017
    Location
    North Carolina
    MS-Off Ver
    2013
    Posts
    16

    Re: Code to merge rows to overcome row height max

    Update: After testing my worksheets, I discovered that the Drawing Text Box I inserted has a 255 character limit. I expect my workbook to exceed this limit regularly, so I converted it to an ActiveX text box, changing the text box properties AutoSize=True, Multiline=True, and Wordwrap=True. Then I altered the macro code to:
    Please Login or Register  to view this content.
    I thought this might be helpful to others finding the same problem...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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