Glad you found it helpful. Your welcome.

--
Regards,
Tom Ogilvy




"PWS" wrote:

A workable if not precise solution.

This does not actually autosize to the exact require row height for
reasons I do not understand. It seems that wrapped text that is a
cell merged across 6 columns of width 10, does not wrap the same as a
60 wide single cell. Hence why in most cases the procedure below does
not produce accurate results - which I need.

Anyone else any ideas?

On Feb 19, 3:15 pm, Tom Ogilvy
wrote:
You can play with this code previously posted by Jim Rech:

Jim Rech

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
End Sub

--
Regards,
Tom Ogilvy



"PWS" wrote:
I have a row of merged cells, into which the user types comments.
These comments could be a few as 2 lines and up to as many as 12
lines. What I would like is a procedure I could run which will resize
the height of the merged cell to just fir the amount of text entered.

Range ("TargetRange").Rows.AutoFit works fine if the cells are not
merged into one.

However if the cells are merged andf the same line of code is run the
cell collapes to just one line.

is there a way to achieve what I require?- Hide quoted text -

- Show quoted text -