where text wraps in a cell, how can the row height be auto..

Stephen Sandor · External Since: May 02, 2006 Posts: 1

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 · External Since: Apr 16, 2006 Posts: 89

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 · External Since: Apr 16, 2006 Posts: 89

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?

Carol · External Since: Aug 14, 2006 Posts: 93

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?

Deb · External Since: Mar 14, 2005 Posts: 130

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 · External Since: Jul 08, 2005 Posts: 16049

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
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

Jim L · External Since: May 31, 2009 Posts: 1

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?

Linda B · External Since: Jul 27, 2009 Posts: 1

"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?

Linda B · External Since: Jul 27, 2009 Posts: 1

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?

Gord Dibben · External Since: Feb 23, 2004 Posts: 9178

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

Wrap Text row>autofit 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
<LindaB.TakeThisOut@discussions.microsoft.com> 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?

Redwren · External Since: Sep 29, 2009 Posts: 2

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 row>autofit 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
> <LindaB.DeleteThis@discussions.microsoft.com> 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?
>
>

Redwren · External Since: Sep 29, 2009 Posts: 2

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 row>autofit 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
> > <LindaB RemoveThis @discussions.microsoft.com> 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?
> >
> >

Gord Dibben · External Since: Feb 23, 2004 Posts: 9178

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
<Redwren.DeleteThis@discussions.microsoft.com> 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 row>autofit 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
>> <LindaB.DeleteThis@discussions.microsoft.com> 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?
>>
>>

Robert Cape Town · External Since: Nov 06, 2009 Posts: 1

"Gord Dibben" wrote:

> 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
> <Redwren.DeleteThis@discussions.microsoft.com> 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 row>autofit 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
> >> <LindaB.DeleteThis@discussions.microsoft.com> 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?
> >>
> >>
> I have carefully read through this discussion twice and I am no better off than when I started. There are obviously some very clever people trying their best to resolve a very simple problem. But clearly without success, as the same user problem is repeated many times. So to the basics.
Admit it, auto fit row height for a merged and wrapped cell (eg cells B5 to
J5) containing an unknown (and variable) number of charaters does not work,
has never worked.
Also, microsoft has known about this since 1997 or earlier.

As this is a pretty much basic function, microsoft must explain why it has
not been fixed.

It is all very well for the clever people to provide macro answers, but most
users do not know and do not use macros. So there has to be a better way that
the ordinary user can easily implement.

Notwithstanding the above, I am a low level macro user and the code provided
above baffles me. (MS Excel 2007)

I challenge microsoft to respond.

>