AutoFit row height not working- Cell not merged, with wrap text, already clicked auto-fit

Question

question

Rachelle-6366 asked Apr 22, '22 Rachelle-6366 edited May 3, '22

AutoFit row height not working- Cell not merged, with wrap text, already clicked auto-fit

I am self taught with Excel so I am hoping I can make sense and that someone can help me.

I have an excel document that I formulated for making pay packages for my job. On a second tab, the contract is filled via the information on tab 1. Part of it is a section that is "free typing" for stipulations that usually take 2-4 lines. I have unlocked the cell, cleared the formatting, clicked "AutoFit" on row height, wrap text, and there are not any merged cells in the location. All that being said, it will not auto-fit, rather I have to go to the contract tab and double click the row and then it does but often will be larger than the text.

Any advice to make this happen automatically as in my experience it usually works this way.

Thank you in advance

office-excel-itpro

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

emilyhua-msft · Apr 25 at 03:30 AM

Hi @Rachelle-6366

Can you share us with a simple sample for better analysis?

Part of it is a section that is "free typing" for stipulations that usually take 2-4 lines.

Does the "Part of it" mean the infromation filled on contract which comes from tab 1?

the contract is filled via the information on tab 1

Do you use the formula to achieve this need or other methods?

Besides, I find you say that you have unlocked the cell, do you use "Protect Sheet" function to restrict operations on locked cells?

Any updates, welcome to post back.

0 ·

Answer 1 · 2022-04-29T08:31:13.843Z

emilyhua-msft answered Apr 29, '22 emilyhua-msft edited Apr 29, '22

Hi @Rachelle-6366

Sorry for the delay.

Then I have B76 pull via formula, from M76.

Does the cell highlighted in green is M76?
I still confused how M76 get the data from B76, do you refer to “Automatic Row Height For Merged Cells with Text Wrap”, then you enter "=B76“ formula in M76?
(Please Note: Since the web site is not hosted by Microsoft, the link may changed without notice. Microsoft does not guarantee the accuracy of this information.)
If yes and M76 does not include the merge cells (Rows with Merged cells will not Autofit or Wrap text), I would suggest you use VBA code to wrap the text in helper column instead.

I have following sample as the gif. On Sheet1, I need to show all text in B2 to B3 which includ merged cells, then I use code to wrap text of cells F2 to F3.

The code is as below. You can copy your workbook, and then press Alt + F11, double click "This Workbook" and copy following code and change the cell range to have a test.

 Sub Autofit()
    
   Worksheets("Sheet1").Range("F2:F3").WrapText = True
    
 End Sub

But, as the method need to add helper coloum, this may cause an extra blank sheet of paper when printing.
It's best to set one or more print areas.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

9.gif (190.6 KiB)

10.gif (94.0 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-04-25T16:26:17.33Z

Rachelle-6366 answered Apr 25, '22 emilyhua-msft converted comment to answer Apr 29, '22

It will not let me attach an excel document but I can send snips of it.

On the first snip, I have highlighted in pink where the initial information is being entered with the settings as the second snip attached.

Then on the next part is where the formula comes in. On the third snip, in B76 in pink, I have the cells merged so I cannot do a wrap text so I am working on the work around. In green, M76 is where I have the formula to copy from the Pay Package, tab 1 and cell L3, but in white so that that cell does the wrap text which inadvertently sizes the column where it won't resize itself due to the merg. Then I have B76 pull via formula, from M76. This works IF I double click the row but then prints odd because of me using the work-around and again, I want it to autofit every time. Also, because of the work-around, it prints with a last blank page.

Thank you for all your help in advance!

![196271-snip-1.png ][2]

[2]: /answers/storage/attachments/196257-snip-1.png

snip-1.png (56.2 KiB)

snip-2.png (41.0 KiB)

snip-3.png (68.5 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-05-03T17:58:05.487Z

Rachelle-6366 answered May 3, '22 Rachelle-6366 edited May 3, '22

I just did my first Macros with this and I believe it worked but it is making the cell quite large. Is there something I did wrong maybe?

Thank you so so much for all your help!

@emilyhua-msft

Update: I tried using it a second time by running macros again and it did not work the second time. I did not do the ('sheet2!A2") because it did not work due to needing another piece to that formula Indirect(ref_text, [a1]).

screenshot-2022-05-03-135349.png (16.6 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question