I have used 2 macros in order to automatically resize the row height in merged cells when using wordwrap. The first code enables the resizing, the second to automatically run the macro when the sheet changes. The problem I have having is that the resizing only occurs if I re-click on the cell after I have entered the data. Can someone tell me what I am doing wrong? Here is the code I am using:
To resize rows (located in the Module)
Sub AutofitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
Application.ScreenUpdating = True
End Sub
To automatically run the macro when a change is made within the sheet (located in the worksheet Object)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Call AutofitMergedCellRowHeight
Application.EnableEvents = True
End Sub
Thanks
Tania
Bookmarks