This post is for the wrong question (It answers another one of my posts though).
When I run the macro on a specific cell, it actually works. Thanks.
However, it only autosizes if the cell needs to be enlarged. If the cell is too large for the amount of verbiage in it, then the cell does not shrink to the appropriate size. Is this something that you can fix?
Also, the macro does not work on the entire sheet. It only works on the cell that you have highlighted when you run it. Is there a way for the macro to search the entire worksheet and autosize all of the merged cells without clicking on each one?
Quote:
Originally Posted by 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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|