AutoFit Row Height

[PWS]

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?

 

[Guest]

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

 

[paul.robinson]

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?

Hi
Do the cells need to be merged? Row and Column Autofit don't work with
merged cells (presumably because the system doesn't know what to do
with the other cells in that row/column)
You can Autofit unmerged cells though.
with merged cells you would have to set the row height/column width.
You can't get at the text length in units like cm (as far as I know)
so you would have to count characters and so on....messy.

regards
Paul

 

[PWS]

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?

 

[PWS]

The quick brown fox jumps over the lazy dog.The quick brown fox jumps
over the lazy dog.The quick brown fox jumps over the lazy dog.The
quick brown fox jumps over the lazy dog.The quick brown fox jumps over
the lazy dog. The quick brown fox jumps over the lazy dog.The quick
brown fox jumps over the lazy dog.The quick brown fox jumps over the
lazy dog.The quick brown fox jumps over the lazy dog.The quick brown
fox jumps over the lazy dog.

If you put the above text into a cell, set it to text wrap, and then
autosize, it does not work accurately. Is there a way around this
does ayone know.

PWS

 

[Guest]

Glad you found it helpful. Your welcome.