how to auto adjust cell height based on text

[Pookiemeister]

On my userform I multi line textbox that only allows 250 characters. Instead of the user typing in text the normal way, like the following:
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

They are copying and pasted the text into the textbox so it looks like the following:
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

In order to see the entire entry in the cell, the user would have to expand the cell height. I have included the section of code that would be handling this task and I have already tried the following way, but this did not work. From what I can see, the sheet/cells are not protected. Any advice would be greatly appreciated. Thank you.

    If rngItem.Offset(, 3).Value = "" Then
        rngItem.Offset(, 3).Select
        rngItem.Offset(, 3).Value = Me.txtbxDescription.Value
        With Selection
            .Rows.AutoFit
        End With
    End If

 

[Pookiemeister]

I just tried the following with no luck. I changed this:

With Selection
     .Rows.AutoFit
End With

to this

rngItem.Offset(, 3).Rows.AutoFit

I realize it is the same thing but I am trying anything that I can think of that might work.

 

[Fluff]

Try

rngItem.EntireRow.AutoFit

 

[Pookiemeister]

That did not work. When it got to your line of code while I was stepping through it, nothing happened. No error messages or anything else. So I even modified your code with following to match what I previously posted:

rngItem.Offset(, 3).EntireRow.AutoFit

and

With Selection
    .EntireRow.AutoFit
End With

Is there something that I need to look for that would prevent this from working? I decided to post the entire Private Sub. Maybe there is something there that may cause this not to work. Thank you Fluff.

Private Sub cmdbtnEnter_Click()
           
    lastRow = Range("C33").End(xlUp).Row + 1
    If rngItem Is Nothing Then
        Set rngItem = Cells(lastRow, "C")
    ElseIf Not rngItem Is Nothing Then
        Set rngItem = Cells(lastRow, "C")
    End If
    If rngItem.Value = "" Then
        rngItem.Select
        rngItem.Value = Me.txtbxQuantity.Value
    End If
    If rngItem.Offset(, 1).Value = "" Then
        rngItem.Offset(, 1).Select
        rngItem.Offset(, 1).Value = Me.txtbxItmNmbr.Value
    End If
    If rngItem.Offset(, 3).Value = "" Then
        rngItem.Offset(, 3).Select
        rngItem.Offset(, 3).Value = Me.txtbxDescription.Value
        'rngItem.Offset(, 3).EntireRow.AutoFit
        'With Selection
        '   .EntireRow.AutoFit
        'End With
    End If
    If rngItem.Offset(, 9).Value = "" Then
        rngItem.Offset(, 9).Select
        rngItem.Offset(, 9).Value = Me.txtbxUntCst.Value
    End If
    If answ > 0 Then
       UserForm_Initialize
    Else
        Unload Me
    End If
End Sub

 

[Fluff]

Do you Wrap Text enabled for that cell?

 

[Pookiemeister]

Yes they are wrapped. Also my apologies, but there is one very important thing that I forgot to mention which may solve this issue. That row is a merged cell column F thru K.

 

[Fluff]

That row is a merged cell column F thru K.

That's the problem. Autofit doesn't work with merged cells.

 

[Pookiemeister]

I'm sure if this is legal within the forum rules but if any one is interested, I found a workaround code which will allow the merge cell to autofit from this site. Thank you again for your help.

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2013_release/autofit-for-merged-cells/55c0ea2c-2d7a-419c-bb1c-23967af98cbf

 

[Fluff]

Glad you sorted it & thanks for the feedback.