Paul, can you check this please?


Gemmini suggests the following:
You are absolutely correct to challenge my previous answer and to point out Microsoft's documentation regarding AutoFit and merged cells. My apologies for providing inaccurate information.
Microsoft's official stance is indeed that you cannot directly use the Rows.AutoFit method on merged cells in the same way you would with unmerged cells. This is a known limitation and can be quite frustrating when working with merged cells.
My previous response was incorrect in stating that cell.MergeArea.Rows.AutoFit would work directly as intended to automatically adjust the height based on wrapped text within a merged cell.
Thank you for bringing this to my attention and correcting my mistake. It's crucial to rely on accurate information, especially from official sources like Microsoft's documentation.
So, how can you achieve a similar effect of adjusting the row height for merged cells with wrapped text in VBA, given this limitation?
You'll need to use a workaround. Here's a common approach:
VBA

Sub AutoWrapAndAdjustMergedCellHeight()
    Dim rng As Range
    Dim cell As Range
    Dim originalWidth As Double
    Dim tempColumn As Long
    Dim tempCell As Range
    Dim lastColumn As Long
    ' Set the range you want to process
    Set rng = ThisWorkbook.Sheets("Sheet1").UsedRange ' Adjust as needed
    Application.ScreenUpdating = False
    For Each cell In rng
        If cell.MergeCells Then
            With cell.MergeArea
                ' Enable text wrapping
                .WrapText = True
                ' Store the original width of the merged area (sum of column widths)
                originalWidth = 0
                For tempColumn = .Column To .Column + .Columns.Count - 1
                    originalWidth = originalWidth + Columns(tempColumn).Width
                Next tempColumn
                ' Find the last used column
                lastColumn = Cells.SpecialCells(xlCellTypeLastCell).Column
                ' Find a temporary unused column
                tempColumn = lastColumn + 1
                ' Copy the merged cell's value to a temporary unmerged cell
                Set tempCell = Cells(.Row, tempColumn)
                tempCell.Value = .Value
                tempCell.WrapText = True
                ' Temporarily set the width of the temporary cell's column
                ' to be approximately the original width of the merged area
                Columns(tempColumn).ColumnWidth = originalWidth / .Columns.Count ' Approximate
                ' Autofit the height of the temporary cell's row
                tempCell.Rows.AutoFit
                ' Apply the autofitted height to all rows in the merged area
                .Rows.RowHeight = tempCell.RowHeight
                ' Clear the temporary cell and reset column width
                tempCell.ClearContents
                Columns(tempColumn).ColumnWidth = 8.43 ' Default width (adjust if needed)
            End With
        End If
    Next cell
    Application.ScreenUpdating = True
    ' Clean up
    Set rng = Nothing
    Set cell = Nothing
    Set tempCell = Nothing
End Sub

Explanation of the Workaround:

  1. Loop through Merged Cells: The code iterates through each cell in the specified range and checks if it's part of a merged area.
  2. Enable Wrapping: Text wrapping is enabled for the merged cell.
  3. Calculate Original Width: The code calculates the total width of the columns that make up the merged area. This is an approximation of the available width for wrapping.
  4. Use a Temporary Unmerged Cell:
    • It finds a temporarily unused column.
    • It copies the value of the merged cell to a single, unmerged cell in this temporary column.
    • It enables text wrapping for this temporary cell.
    • It sets the column width of the temporary cell's column to be roughly equivalent to the total width of the merged columns. This is to simulate the wrapping behavior within the merged cell's width.

  5. Autofit Temporary Cell: The tempCell.Rows.AutoFit method is used on this unmerged cell, which works correctly.
  6. Apply Height to Merged Rows: The RowHeight of all the rows within the original merged area is then set to the RowHeight of the autofitted temporary cell.
  7. Clean Up: The temporary cell's content is cleared, and the temporary column's width is reset to a default value.

Important Considerations for the Workaround:

  • Approximation of Width: Setting the temporary column width as an average might not be perfectly accurate if the individual column widths within the merged area are very different. This could lead to slightly different wrapping behavior.
  • Temporary Column: The code finds a column after the last used column. Ensure you don't have data extending very far to the right that might conflict with this temporary column.
  • Performance: This workaround involves more steps and might be slower than a direct AutoFit (if it were possible), especially for a large number of merged cells.


I have no idea regarding the concept of "direct" autofit and this supposed "indirect" autofit.