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?

Option Explicit
sub DoAll()
dim myCell as range
for each mycell in selection.cells
mycell.select
call AutoFitMergedCellRowHeight
next mycell
end sub

Might work for you.

Select the range to fix first, then run the DoAll code.

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

Thank you Dave.

I'll get back to you as soon as I have the formula put in the right place by
a friend. I just don't know how to it. No experience. Too late now it's 9:27
PM in France. Pacific time ? only 11:27?


magnolia 71

"Dave Peterson" wrote:

Option Explicit
sub DoAll()
dim myCell as range
for each mycell in selection.cells
mycell.select
call AutoFitMergedCellRowHeight
next mycell
end sub

Might work for you.

Select the range to fix first, then run the DoAll code.

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

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?

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

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

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

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