Row height and Merged Cells

[Darin Kramer]

Hi guys... heres the problem...

A2 contains a question number (short text)
B2 and C2 are merged and contain a question (long text)
The answer is going in Cell D2 (longest or medium length text)

As you are dealing with Merged Cells, auto row height format (ie double
clicking when the entire row) does not work. So double clicking on row
height arrows on row 2 would not solve the issue. I can manually select
row height to correctly space the question, but I have no idea how long
the answer will be, ie User could type a long answer in cell D2, and
Excel does not automatically adjust row height to answer cell.
Effectively row height needs to be greater of Question height (Merged B
and C) or Answer height (Row D)
All Cells are wrapped text.

There are lots of questions, and not practical for user to manually
resize.

Any ideas...?

Thanks

D

 

[Darin Kramer]

Tom....?

 

[Tom Ogilvy]

this code was previously posted by Jim Rech (also in KPMG) to address this
type problem. See if you can adjust it to your situation:

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

 

[Darin Kramer]

Hi Tom,

I know its been a while on this query, but Im only getting to look at
the answer your proposed now. Good news is it works, BUT only if you
selec the exact cell which is the problematice one (ie the merged one)
How can I get it to run for all cells... ie look at all cells, and IF
its a merged one then run the macro...?

Really really appreciate your help.

Kind regards

Darin