Autofit doesn't work with merged cells

[Guest]

When I merge two cells together across columns & select wrap feature, the row
height doesn't adjust to accommodate all the words...even when I select
autofit. I have to manually drag the row number to see that all the words
show.

 

[Dave Peterson]

Yep.

Jim Rech wrote a macro called AutoFitMergedCellRowHeight that you may like:
http://groups.google.com/groups?threadm=e1$1uzL1BHA.1784@tkmsftngp05

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Appreciate the help...did get the macro to run, but the limitation to only
the active cell makes it too cumbersome for my needs. Thanks though...ugh!
I wish it worked.

 

[Dave Peterson]

Maybe you could just use Jim's code and loop through your cells.

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Set myRng = Selection
For Each myCell In myRng.Cells
Application.Goto myCell
Call AutoFitMergedCellRowHeight
Next myCell
End Sub

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

Select range and run the macro that calls Jim's code.