Autosize Merged Cell

[TreeHugger1]

Is there anyway to run a macro or change a setting to autosiz
(autoadjust the height) of any merged cells in a spreadsheet. I have th
autowrap option checked on the merged cells. I am running Excel 2007.

I am not very familiar with VBA code, but I understand how to create ne
modules and apply them to a spreadsheet

 

[Claus Busch]

Hi,

Am Mon, 21 Jan 2013 23:48:22 +0000 schrieb TreeHugger1:

Is there anyway to run a macro or change a setting to autosize
(autoadjust the height) of any merged cells in a spreadsheet. I have the
autowrap option checked on the merged cells. I am running Excel 2007.

untested:

Sub AutoFitMergedCellRowHeight()
'passt die Zeilenhöhe bei verbundenen Zellen automatisch an
'von Hans Herber
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
Dim iX As Integer
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
iX = iX + 1
Next
MergedCellRgWidth = MergedCellRgWidth + (iX - 1) * 0.71
.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
Claus Busch