Wrap text in merged cells VBA

[dme82]

I have merged cells that need to wrap text. I used the VBA code several of
you provided and it is working great for the most part. The problem I am
running into is when I have more than one set of merged cells on the same
row. I have applied text wrap to each of them. Let's say I type in several
lines of text in the first merged cell unit and it wraps the text perfectly
and auto fits the cell. However, if I type only a single line of text in the
next merged cell unit in the same row, the whole row autofits to that single
line and covers up my multiple lines of text that I just typed in the
previous merged cell unit. Any suggestions on what may be the problem? If I
need to try to explain further I will.

Much thanks!

 

[Jim Rech]

I was involved in creating the original merged cell autofit macro that
circulates here. That macro did not reduce row heights for just the reason
you cite. I post it here, but since you are using a modified version, it
may be that your version does things this one does not. So, Fwiw:

''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height (because another
'' merged cell on the same row may needed a greater height
'' than the active cell).
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 .Cells(1).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


--
Jim
|I have merged cells that need to wrap text. I used the VBA code several of
| you provided and it is working great for the most part. The problem I am
| running into is when I have more than one set of merged cells on the same
| row. I have applied text wrap to each of them. Let's say I type in several
| lines of text in the first merged cell unit and it wraps the text
perfectly
| and auto fits the cell. However, if I type only a single line of text in
the
| next merged cell unit in the same row, the whole row autofits to that
single
| line and covers up my multiple lines of text that I just typed in the
| previous merged cell unit. Any suggestions on what may be the problem? If
I
| need to try to explain further I will.
|
| Much thanks!

 

[Rilo Ravestein]

Is there a way to let excel adjust the row height according to the largest merged cell in a row? To let excel search the row for merged cells and adjust the row so that the maximum needed row height is set.

I have more then one merged cells in a row and the text in all cells have to be completely visible. I have tryed your codes, but they both only "look" at the current cell.