Both of these routines go into a general module--they don't go behind the
worksheet.

68magnolia71 wrote:

Hello Dave,

I'm desparate. Did not work.
I opened a new worksbook, opened the macro window on sheet 1, named the
macro DoALL, put ALL your text, all signs included, in the windows. I erased
the extra line "Sub DoAll". On the worksheet I put 3 merged cells made of 4
basic cells and ran the DoAll macro.
- A windows appeared " Compilation Error, only comments after End Sub, End
Function. " yellow highlighted. I put a (') in front of the line "Sub Auto
..., and ran the macro. New message: " Compilation Error, Sub or Function not
defined".
All this is beyond my knowledge. It looks like highway signs where you need
to know the road to find the right signs.
Sorry Dave for the trouble and thanks for your help.
magnolia71

"Dave Peterson" wrote:

Did you try my suggestion?

I put some merged cells on a worksheet and selected them.

Then I ran this DoAll macro and it worked fine:


Option Explicit
Sub DoAll()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Select
Call AutoFitMergedCellRowHeight
Next myCell
End Sub

'this is Jim Rech's code (included only for completeness/
'ease of copy|pasting.

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


68magnolia71 wrote:

Unfortunately I couldn't find someone able to "connect" both macros. Or at
least it didn't work. Question how to do it? Which line to suppress or add?

Thanks for any help.

magnolia

"68magnolia71" wrote:

Bernie, sent me a macro (Jim macro) that automatically sets the row height
based on text length, given the column width. The works well. Unfortunately I
have to select the merged cells one by one and run the macro. It takes a long
time as I have about 20 groups of 3 rows with 4 murged cells in each row.
If I select more than one cell the macro doesn't work. So the question is
how to tell the macro to select all the murged cells and have the height set.
I can easely select all the rows with murged cells with a macro but how
connect with Jim's VBA macro?

--

Dave Peterson


--

Dave Peterson