Auto fit wrapped and merged cells in entire worksheet Options

[danbridgland]

Hi,

I'm using SQL Server Reporting Services to produce reports, i'm then
exporting the reports to excel, only I've run into a known problem
with excel, Merged cells and wraped text dont mix together very well
in excel - the row height is not preserved.


Ive found a fix posted in this group posted by Jim Rech in 2002
(http://groups.google.com/group/microsoft.public.excel.programming/
browse_thread/thread/a5cbe0ee8e6c2a10/93c6bca447bd8902?
#93c6bca447bd8902) in the form of a macro, the macro adjusts row
height for a selected cell so that its contents fits. however, this
macro is designed to work only on a single selected cell.


being a complete excell macro/vba noob, I'm hoping someone out there
can help me fix this macro so that it scans every cell/merged cell in
a worksheet and adjusts the row height to fit its contents.


Here is the macro created by Jim Rech


''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 .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


Thanks in advance for any assistance.


Regards
Dan