Thanjks Tom - all is working well now.


"Tom Ogilvy" wrote in message
...
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
' add this line:
Range("RepRiskDetail").Select
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


"sacrum" wrote in message
...
Hi Tom,

I'm having rpoblems with the script....

My worksheet has a sub called Form which extracts choice and put onto
form.

The merged cells are in worksheet set as wrep and are labelled
RepRiskDetail

Would you kindly help me put that context into the script you posted?
Ideally I would want this function within the sub form.

Chris

"Tom Ogilvy" wrote in message
...
Merged cells don't autofit. You might look at 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


"sacrum" wrote in message
...
I have a feature in my workbook that produces a report view on one
worksheet
by copying certain data fields into labelled cells. One receiving
cell
is
comprised of merged cells - when it receives text/data only some of it
is
visible as the cell is too small - tried several things to no avail.
Is
there a for the labelled cell to auto fit the the received data or is
this
limited by excel?

Tia