Wrap text and autofit in merge cells

[NekHeN]

Hello, I want to write a VBA to wrap text and autofit text in merged cells (A1 to C1). I tried different approaches but the merged cells only show a little bit of the text after the wrap text and I end up needing to adjust the row height manually.

[Aussiebear]

Maybe try this? Note you will have to change the range to suit yourself.

Sub AutoWrapAndAutofitMergedCells()
    Dim rng As Range
    Dim cell As Range
    ' Set the range you want to process. For example, the entire used range of Sheet1.
    ' Adjust as needed.
    Set rng = ThisWorkbook.Sheets("Sheet1").UsedRange
    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the cell is part of a merged area
        If cell.MergeCells Then
            ' Enable text wrapping
            cell.WrapText = True
            ' Autofit the row height of the merged area
            cell.MergeArea.Rows.AutoFit
        End If
    Next cell
    ' Clean up  Set rng = Nothing
    Set cell = Nothing
End Sub

[Aflatoon]

FYI, cross-posted: https://www.excelforum.com/excel-pro...rge-cells.html

[Paul_Hossler]

Hello, I want to write a VBA to wrap text and autofit text in merged cells (A1 to C1). I tried different approaches but the merged cells only show a little bit of the text after the wrap text and I end up needing to adjust the row height manually.

4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3


https://su'You cannot use the AutoFit feature for rows or columns that contain merged cells in Excelpport.microsoft.com/en-us/topic/you-cannot-use-the-autofit-feature-for-rows-or-columns-that-contain-merged-cells-in-excel-34b54dd7-9bfc-6c8f-5ee3-2715d7db4353"

'You cannot use the AutoFit feature for rows or columns that contain merged cells in Excel"

[Aussiebear]

Thank you Paul.

[otis5842]

Are there any other formatting rules or protections applied to the worksheet?

[Aussiebear]

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.