define cell for autofit of merged cells
 

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!

define cell for autofit of merged cells
 

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

"erin" <espencer@wdprosdotcom wrote in message
...
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!

define cell for autofit of merged cells
 

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

"erin" <espencer@wdprosdotcom wrote in message
...
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!




.

define cell for autofit of merged cells
 

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

--
Regards,
Tom Ogilvy


"erin" <espencer@wdprosdotcom wrote in message
...
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

"erin" <espencer@wdprosdotcom wrote in message
...
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!




.