Auto Adjust Row Height based on Wrapped Text Sentences

[nhinx]

Dear Excel Experts,

I tried the code below to automatically adjust the row height in merged cell from A10 to G10. For example, if my sentence is justified in 2 lines only the row height will adjust for 2 liner sentences. Then if my write ups could go longer (example: 6 lines sentences) it should also adjust based on that data.

Range("A10:G10").EntireRow.AutoFit
Range("A10:G10").WrapText = True

However, the code doesn't seems to work.

Can you help me resolve this problem?

Thanks,
Nhinx

 

[nhinx]

Hi Fluff,

I got the code below from the link that you gave and it works. Thank you for giving me the link. This code is awesome.

Private Sub Worksheet_Activate()

    
Dim j As Long
    Dim n As Long
    Dim i As Long
    Dim MW As Double 'merge width
    Dim RH As Double 'row height
    Dim MaxRH As Double
    Dim rngMArea As Range
    Dim rng As Range
     
    Const SpareCol  As Long = 26
    Set rng = Range("A10:G10" & Range("C" & Rows.Count).End(xlUp).Row)
    
    With rng
        For j = 1 To .Rows.Count
             'if the row is not hidden
            If Not .Parent.Rows(.Cells(j, 1).Row).Hidden Then
                 'if the cells have data
                If Application.WorksheetFunction.CountA(.Rows(j)) Then
                    MaxRH = 0
                    For n = .Columns.Count To 1 Step -1
                        If Len(.Cells(j, n).Value) Then
                             'mergecells
                            If .Cells(j, n).MergeCells Then
                                Set rngMArea = .Cells(j, n).MergeArea
                                With rngMArea
                                    MW = 0
                                    If .WrapText Then
                                         'get the total width
                                        For i = 1 To .Cells.Count
                                            MW = MW + .Columns(i).ColumnWidth
                                        Next
                                        MW = MW + .Cells.Count * 0.66
                                         'use the spare column and put the value, make autofit, get the row height
                                        With .Parent.Cells(.Row, SpareCol)
                                            .Value = rngMArea.Value
                                            .ColumnWidth = MW
                                            .WrapText = True
                                            .EntireRow.AutoFit
                                            RH = .RowHeight
                                            MaxRH = Application.Max(RH, MaxRH)
                                            .Value = vbNullString
                                            .WrapText = False
                                            .ColumnWidth = 8.43
                                        End With
                                        .RowHeight = MaxRH
                                    End If
                                End With
                            ElseIf .Cells(j, n).WrapText Then
                                RH = .Cells(j, n).RowHeight
                                .Cells(j, n).EntireRow.AutoFit
                                If .Cells(j, n).RowHeight < RH Then .Cells(j, n).RowHeight = RH
                            End If
                        End If
                    Next
                End If
            End If
        Next
        .Parent.Parent.Worksheets(.Parent.Name).UsedRange
    End With




End Sub

 

[Fluff]

Glad the link helped & Thanks for the feedback