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.
Printable View
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.
Maybe try this? Note you will have to change the range to suit yourself.
Code: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
FYI, cross-posted: https://www.excelforum.com/excel-pro...rge-cells.html
Quote:
Originally Posted by NekHeN
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"
Thank you Paul.
Are there any other formatting rules or protections applied to the worksheet?
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
Code: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:
- Loop through Merged Cells: The code iterates through each cell in the specified range and checks if it's part of a merged area.
- Enable Wrapping: Text wrapping is enabled for the merged cell.
- 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.
- 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.
- Autofit Temporary Cell: The tempCell.Rows.AutoFit method is used on this unmerged cell, which works correctly.
- 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.
- 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.