mag-text in merged cells-automatic row heigh

Jim's code checks to see if it's already large enough.

.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)

My bet is that Jim wrote the code so that it wouldn't harm the rowheights that
were set manually by the user. Or by conflicting merge areas.

Imagine you have merged cells that in columns A1:C1 that need to be 60 pixels
high. But you have E1:G1 to be 45 pixels high. Do you want your routine to
change the rowheight just because it does E1:G1 after A1:C1???

If you don't have that situation, maybe you could just autofit the rowheights
before your code starts--you've seen that this collapses the rows. You can use
this behavior to your advantage.



68magnolia71 wrote:

Helmo Dave,

I've done what I wrote below. Just one question: why does this macro not
work backwards? It means if the rows of the murged cells are too hight for
the text the macro leaves it like it is. The macro only inceases the height
never diminishes it.

68magnolia71

"68magnolia71" wrote:

Hello Dave,
IT WORKED!
I've found a workbook with a macro on " general" page named maodule 1. For
some reason a page module 2 opened. I give a new name & Pasted both maros on
it and run it.
It didn't like "Option Explicit "and "Sub DoAll()" which I suppressed and
eventially it worked. Its not even neccessary to select the cells, since the
"new" macro works with the selection of the rows.
The next step is convince the macro to select itself the murged cells and
set the height. At the end I'll have a button on the worksheet.

Thanks Bernie, Dave and Jim.
68magnolia71 5:44PM

"Dave Peterson" wrote:

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


--

Dave Peterson