I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

BTW, the code was adapted from an old Jim Rech post. Forgot to mention this.
Credit to him for the concept.

Greg

"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

I need to do exactly this in Excel 2007. I am not able to get the row auto
height to work. I can't even double click on the row header to make it
happen. Only dragging each individual row is working. I would greatly
appreciate some assistance getting this module to work in 2007. Thanks much!



"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

I'm not sure whats happened there but drop this in to the worksheet code
module:

Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Rows.EntireRow.AutoFit
End Sub
--------------------
every row you click should autofit!


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=37732

I am having the same problem. Is it an Excel 2007 glitch?
Did you find a solution that works?

THX
Carol

"Datadonna" wrote:

I need to do exactly this in Excel 2007. I am not able to get the row auto
height to work. I can't even double click on the row header to make it
happen. Only dragging each individual row is working. I would greatly
appreciate some assistance getting this module to work in 2007. Thanks much!



"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

I am having this problem, also.

Since I don't know what the "worksheet code module" is, the above does not
help me.

Need more explaination.

"Datadonna" wrote:

I need to do exactly this in Excel 2007. I am not able to get the row auto
height to work. I can't even double click on the row header to make it
happen. Only dragging each individual row is working. I would greatly
appreciate some assistance getting this module to work in 2007. Thanks much!



"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Deb wrote:

I am having this problem, also.

Since I don't know what the "worksheet code module" is, the above does not
help me.

Need more explaination.

"Datadonna" wrote:

I need to do exactly this in Excel 2007. I am not able to get the row auto
height to work. I can't even double click on the row header to make it
happen. Only dragging each individual row is working. I would greatly
appreciate some assistance getting this module to work in 2007. Thanks much!



"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

--

Dave Peterson

I got this code to work, thank t=you very much, I have a question however
which is:
I have a number of merged cell instances within the same worksheet and
workbook where I need this functionality. I cannot get this code to work for
additional instances. Can you help?
Thank you.

"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

I have a row with merged cells and I am trying to get the height to
automatically adjust. How do I get this to happen?
Linda B.

"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

Make appropriate changes to the range in Greg's code.

Wrap Text rowautofit must be enabled to start with.

If stuck, please post details of your merged cells area(s)



Gord Dibben MS Excel MVP


On Mon, 27 Jul 2009 12:01:02 -0700, Linda B
wrote:


I have a row with merged cells and I am trying to get the height to
automatically adjust. How do I get this to happen?
Linda B.

"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

HELP! Just need to have rows auto fit contents (expand). Do not understand
the complicated code referenced in this post or even where to copy and insert
this code. Why doesn't the "Auto fit Row Height" option work under
formatting?

"Gord Dibben" wrote:

Make appropriate changes to the range in Greg's code.

Wrap Text rowautofit must be enabled to start with.

If stuck, please post details of your merged cells area(s)



Gord Dibben MS Excel MVP


On Mon, 27 Jul 2009 12:01:02 -0700, Linda B
wrote:


I have a row with merged cells and I am trying to get the height to
automatically adjust. How do I get this to happen?
Linda B.

"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

More info: I'm using Excel 2007 - several columns are merged and merged and
text wrap boxes are checked.

"Redwren" wrote:

HELP! Just need to have rows auto fit contents (expand). Do not understand
the complicated code referenced in this post or even where to copy and insert
this code. Why doesn't the "Auto fit Row Height" option work under
formatting?

"Gord Dibben" wrote:

Make appropriate changes to the range in Greg's code.

Wrap Text rowautofit must be enabled to start with.

If stuck, please post details of your merged cells area(s)



Gord Dibben MS Excel MVP


On Mon, 27 Jul 2009 12:01:02 -0700, Linda B
wrote:


I have a row with merged cells and I am trying to get the height to
automatically adjust. How do I get this to happen?
Linda B.

"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

Plain and simple...................If you have merged cells in the range,
Autofit won't work even with wraptext enabled.

You will need the code if you insist upon using merged cells.

The developers added the merge cells feature in Excel 97 without thinking
about row autofit functionality.

Have not bothered to correct since.


Gord


On Tue, 29 Sep 2009 11:19:01 -0700, Redwren
wrote:

HELP! Just need to have rows auto fit contents (expand). Do not understand
the complicated code referenced in this post or even where to copy and insert
this code. Why doesn't the "Auto fit Row Height" option work under
formatting?

"Gord Dibben" wrote:

Make appropriate changes to the range in Greg's code.

Wrap Text rowautofit must be enabled to start with.

If stuck, please post details of your merged cells area(s)



Gord Dibben MS Excel MVP


On Mon, 27 Jul 2009 12:01:02 -0700, Linda B
wrote:


I have a row with merged cells and I am trying to get the height to
automatically adjust. How do I get this to happen?
Linda B.

"Greg Wilson" wrote:

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

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

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing 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 Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

Ruth

Do you have a question about this previous post you tacked onto?

Short answer...............merged cells do not allow row autofit unless you
employ VBA event code.


Gord Dibben MS Excel MVP

On Fri, 22 Jan 2010 15:07:01 -0800, Ruth <Ruth @discussions.microsoft.com
wrote:



"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

I had the same issue. I found a work around for my situation. I made the
column as wider instead of merging cells, then when you select wrap text the
automatic row height works. It may not work for all but does for me.

"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

Thanks Ruth. I tried this and it worked for me. I unmerged the cells and
then double clicked for auto row height and it worked.

"Ruth" wrote:

I had the same issue. I found a work around for my situation. I made the
column as wider instead of merging cells, then when you select wrap text the
automatic row height works. It may not work for all but does for me.

"Stephen Sandor" wrote:

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

I need help for a very similar problem:

NO merged cells
NO specialized formatting, and format is consistent across spreadsheet
cells are set to "wrap text"

When I click on "autofit row height", 90% of the rows become the correct
height, while 10% do not. I have a few thousand rows of data, and 26 columns
with varying amounts of text in them. Sometimes the row becomes too short,
sometimes too tall, and it can happen no matter how much or how little text
is in it.

Can I repeat that I DO NOT have any merged cells? I have searched online
for hours and no one seems to have an explanation or a solution.