Split cell text to another row due to maximum row height

  • Hello all,


    Good day, love the new look for the forum.


    hope this is not repeated question as I could not find the similar one.


    I have an excel with an automated update from SharePoint list, this list provides me data from column A to G (worksheet named as Master Table). The data that I have in column F is generate as text which is most of the time having lot of characters and its turn the cell height become maximum number that excels can provide (409.5) this condition cause a problem that every time I save the file to pdf some of the text is cut and missing from the preview.


    is there any VBA code that could split the text in column F whenever the height is maximum so that the rest of the text will go to the next row.


    I used the autofit function but it is not enough to cover the needs,

    can someone help me out?


    here is my code with autofit :


    :):)

  • Hello tbh,


    Not sure if you have solved this yet?


    The approach I would take would be to calculate the number of characters required to fill the cell then use an if len(x) > allowed-characters then cut the text as required. This depends upon the width of the column, for example I just tested a width of 100 and the maximum number of characters I could fit with the height at maximum were 2533 using tahoma 12 pt font.


    The code below checks the length of the text in the F2 cell, rounds up the integer and then copies the string over back into the required number of cells ensuring it each does not exceed the maximum number allowed.


    If you wanted to be fancy you could search for the nearest full stop to the end in each string using the instrrev, but it would be a more complicated solution (if you were confident it will never require more than 2 cells then finding the full stop closest to the end of the first cell would be easy enough).


    HTH

    Justin