Automatically Adjusting Row Height in Merged Cells
 

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

How can I make this work on 3 consecutive tabs in the same workbook?

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

I'm having a problem with the code not following through on all lines
(23-43)...hints?

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

Here's another stumbling block I believe I failed to mention. The merged
cells have a VLOOKUP function in them. If I enter a number to be 'looked
up', then go back and insert the same function again, the row will
expand...otherwise it will stay as one line. Hope you can help.

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

Worksheet_change event is not triggered by a calculation.

Maybe worksheet_calculate but I don't have time to test.


Gord

On Wed, 1 Aug 2007 19:30:02 -0700, MGC wrote:

Here's another stumbling block I believe I failed to mention. The merged
cells have a VLOOKUP function in them. If I enter a number to be 'looked
up', then go back and insert the same function again, the row will
expand...otherwise it will stay as one line. Hope you can help.

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

You could paste the code into each worksheet module.

Or you could place it in Thisworkbook module and change the event type to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Would then work on all worksheets.


Gord

On Wed, 1 Aug 2007 18:12:02 -0700, MGC wrote:

How can I make this work on 3 consecutive tabs in the same workbook?

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

No hints from me unless those rows are calculated values which do not trigger
change events.


Gord

On Wed, 1 Aug 2007 18:36:02 -0700, MGC wrote:

I'm having a problem with the code not following through on all lines
(23-43)...hints?

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

Gord:

I discovered this evening that if I record a macro with a shortcut key to
reinsert the VLOOKUP function already in the cell that this works. Thanks
again for all your help!

"Gord Dibben" wrote:

Worksheet_change event is not triggered by a calculation.

Maybe worksheet_calculate but I don't have time to test.


Gord

On Wed, 1 Aug 2007 19:30:02 -0700, MGC wrote:

Here's another stumbling block I believe I failed to mention. The merged
cells have a VLOOKUP function in them. If I enter a number to be 'looked
up', then go back and insert the same function again, the row will
expand...otherwise it will stay as one line. Hope you can help.

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

I've tried to insert the code as was suggested. When I go to test it out, I
get a Run-time error '1004': Unable to set the Column Width property of the
Range class, and it point to the row of code "e.ColumnWidth = MrgeWdth" which
is about half way through the code that was copied/pasted. The end result is
that the merge of the cells seems to be lost as the cells then have the grid
lines shown again for columns and rows and all of the text is placed in the
top left most cell and that cell in fact does have the row height auto
adjusted. Any ideas on why this happens?

"MGC" wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?

Automatically Adjusting Row Height in Merged Cells
 

Is the sheet protected?

When I protect the sheet and enter text in an unlocked merged cell I get the
same error message.


Gord Dibben MS Excel MVP

On Mon, 1 Oct 2007 08:40:00 -0700, KAC wrote:

I've tried to insert the code as was suggested. When I go to test it out, I
get a Run-time error '1004': Unable to set the Column Width property of the
Range class, and it point to the row of code "e.ColumnWidth = MrgeWdth" which
is about half way through the code that was copied/pasted. The end result is
that the merge of the cells seems to be lost as the cells then have the grid
lines shown again for columns and rows and all of the text is placed in the
top left most cell and that cell in fact does have the row height auto
adjusted. Any ideas on why this happens?

"MGC" wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?