define cell for autofit of merged cells

  • Thread starter Thread starter erin
  • Start date Start date
E

erin

Hello,
I've got Jim Rech's sub for autofit of merged cells,
http://www.google.com/groups?threadm=uGMQVjd0CHA.2296%
40TK2MSFTNGP10

I want to use sub to adjust row 16 before print. I've
put it in the ThisWorkbook object. My problem is defining
row 16. I've tried...

1) defining CurrCell with
Set CurrCell = Worksheets("Sheet1").Range("A16:H16")

2) using
With Range ("A16:H16")
instead of
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea

3) both of the above with ("A16") instead of ("A16:H16")

4) generally butchering a perfectly good working sub in
multiple ways :-)

A simple fix I'm sure, but I'm stumped.

Thanks in advance!
 
Does row 16 contain merged cells?

If so, are there multiple merged areas or only one merged area (A16:H16 for
example).
 
Row 16 has only 1 merged area, A16:H16.
-----Original Message-----
Does row 16 contain merged cells?

If so, are there multiple merged areas or only one merged area (A16:H16 for
example).

--
Regards,
Tom Ogilvy




.
Click to expand...
 
The easiest solution would be

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range, rngActive as Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
set rngActive = selection
Range("A16").Select
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
rngActive.Select
End Sub
 
Back
Top