VBA Code To Autofit The Row Height Of Merged Cells

[Excel Fox]

David, here's another suggestion. Replace your entire code with this.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cM As Range
    Dim AutoFitRng As Range
    Dim MergeWidth As Single
    Dim NewRowHt As Single
    Dim str01 As String
    str01 = "OrderNote"

    If Not Intersect(Target, Range(str01)) Is Nothing Then
        Application.ScreenUpdating = False
        Set AutoFitRng = Range(str01).MergeArea
        For Each cM In AutoFitRng.Cells
            MergeWidth = cM.ColumnWidth + MergeWidth
        Next cM
        
        With Worksheets.Add
            .Columns(1).ColumnWidth = MergeWidth
            With .Cells(1)
                .Value = AutoFitRng.Cells(1).Value
                .WrapText = True
                .EntireRow.AutoFit
            End With
            NewRowHt = .Rows(1).Height
            Application.DisplayAlerts = False
            .Delete
            Application.DisplayAlerts = False
        End With
        With AutoFitRng
            .WrapText = True
            .RowHeight = NewRowHt
        End With
        Application.ScreenUpdating = True
    End If

End Sub

[David Michael]

Hi, I'm sorry to report that this new code just gets me back to the same place as the original code, i.e. it works in terms of autosizing the row height, but limits that row height to 6-8 rows worth of content, depending on the combined width of the merged cells.

I'm starting to think that this might not be possible and so am considering a workaround: in addition to using this code, which will cover any use of the worksheet where the data entered in the field is 6-8 rows or less, I could put a button above the field with something like "Enlarge" written on it, and assign a macro that expands the row height to allow for 10 rows of content.

Everything I just described I can do, but the macro would work better if, rather than just changing the row height to 170 pixels (10 rows), it could identify the current row height and add 17 pixels (1 row). In this way, each press of the "Enlarge" button would expand the row height to allow 1 more row's worth of content to be displayed. Is it possible to build a macro that does that? If it helps, the sheet I'm working on is called "Loss Evaluation", the field is named "OrderNote" and the cell range is A18:L18.

Thank you!