Excell autofit row height function

[brg]

I'm creating a worksheet where I want users to fill in free text data.
the autofit row height seems to work where individual cells are concerned.
My difficulty is that where cells have been merged (within a single row) the
function appears not to work.

any suggestions ?

 

[Greg Wilson]

Paste this to the worksheet's code module: Right click the sheet tab and
select View Code and then paste the code to the module. If you are protecting
the sheet then it needs to be modified. This is an adaption of code
originally written by Jim Rech.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
Application.ScreenUpdating = True
End If
End With
End Sub

Regards,
Greg