[Solved] Printing: Dynamic cell resizing for variable length

  • Your assistance is greatly appreciated for this problem.

    For print purposes (and for reasons of easily creating contiguous print output) I have created a summary worksheet that pulls together data from many areas within in a large, complex workbook. This summary sheet is a temporary "scratch" sheet created from a template, is populated with variable-length data, and is then destroyed after the user prints the sheet. I have done this so that I can easily pre-format individual cells (for height, width, font, word-wrap within a cell, etc.). Some cells may have up to a paragraph of explanatory text based on decisions the user has made in other parts of the workbook. These same cells may also have only several words if the user has made a different pre-selection.

    And this is my problem. Ideally, I would like to have the cells resize dynamically to accommodate just the text they contain, so that the final print output looks clean and there is no excessive white space. Right now, I have to pre-fix and pre-format cells to the longest possible text entry.

    I would rather not code VBA to instantiate Word from this Excel spreadsheet, even though I know this problem could be solved by running a simple merge function within Word. Is there any way to dynamically resize an Excel cell to accommodate variable length text?

  • Welcome to the Forums.
    Format-Cells-Alignment-WrapText will work if you are entering data or copy-pasting data but not if you are linking data (ie in cell b10 you have formula =a1)

  • Thanks Doug. The problem I have though is that the summary worksheet is made up entirely of linked data from multiple areas of a massive workbook.

    These linked areas primarily provide text that populate various cells in the summary worksheet. I have already used Format>Cells>Alignment>Wordwrap Text, but because the text that populates the cells can vary from several words to a paragraph, I now have to adjust row height in specific cells to the longest text string. This is less desirable than an approach that would automatically resize a cell to the length of the text string.

    Microsoft Word already does this when you do mail merge. Static text after a merge field automatically moves to accommodate the entire length of the merge field - whether the merge field is 5 characters, or 500.

    Can this be be done in Excel?

  • If you placed some VBA code such as the following into a Workbook_BeforePrint macro then (assuming you have youre columns sized to the widths you want) should automatically reset the row heights before the file is sent to the printer.


    (Note: the last line does nothing more than prevetn the sheet from being left with all cells selected.)

  • Quote

    Originally posted by thomach
    Nice touch, Will, and thank you. I didn't know that worked without selecting the specific rows to format.

    OzGrid is one great place to keep learning new tricks. :yes:

    Thank Richie :biggrin: - He pointed it out to me :wink2:

  • Thank you Ritchie, Will and Thomach for your simple and elegant solution to this problem!

    I ran this VBA code as a small sample:

    With Cells
    Cells(1, 1) = "Items Needed:"
    Cells(2, 1) = TextBox12.Text
    Cells(3, 1) = TextBox32.Text
    Cells(4, 1) = TextBox17.Text
    Cells(5, 1) = TextBox15.Text
    Cells(8, 1) = TextBox19.Text

    End With

    on the summary sheet and it automatically resized each row to accommodate variable text returned from each of the above TextBoxes. The only thing I had to do was ensure that Wordwrap was enabled under the format command for each of the above cell references. I then enabled a Worksheets.PrintPreview statement in a next line to allow the user to do any final adjustments prior to printing.

    You guys are great! Thanks again.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!