Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
68magnolia71
 
Posts: n/a
Default mag-text in merged cells-automatic row heigh

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?
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
68magnolia71
 
Posts: n/a
Default

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

  #4   Report Post  
68magnolia71
 
Posts: n/a
Default

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?

  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #6   Report Post  
68magnolia71
 
Posts: n/a
Default

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

  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #8   Report Post  
68magnolia71
 
Posts: n/a
Default

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

  #9   Report Post  
68magnolia71
 
Posts: n/a
Default

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

  #10   Report Post  
Dave Peterson
 
Posts: n/a
Default

Glad you got it working.

68magnolia71 wrote:

Hello Dave,
IT WORKED!


<<snipped


  #11   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I Excel to re-size when wrapping text in merged cells Samjoy Excel Discussion (Misc queries) 4 February 4th 05 01:47 AM
Rows with merged cells are not adjusting even w/ Wrap Text and au. mkern20 Excel Discussion (Misc queries) 1 January 4th 05 09:10 PM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
How do I get merged cells to display all text. Excel problem Excel Discussion (Misc queries) 2 November 30th 04 05:29 AM
How do I "Wrap Text" & "Autofit" within Merged Cells in Excel? 6-shooter Excel Worksheet Functions 3 October 31st 04 12:14 AM


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"