Merged cells won't Autofit row height

Hpyifur · External Since: Jan 12, 2007 Posts: 2

When I merge cells and then choose the wrap text option the Autofit function
no longer works. Is there a way to get around this without manually sizing
the row each time?

RagDyer · External Since: Jun 14, 2004 Posts: 762

*Unmerge* the cells and then use "Center Across Selection" from:

<Format> <Cells. <Alignment> tab,
Expand the "Horizontal" window and choose "Center Across Selection".
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Hpyifur" <Hpyifur.TakeThisOut@discussions.microsoft.com> wrote in message
news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
> When I merge cells and then choose the wrap text option the Autofit
> function
> no longer works. Is there a way to get around this without manually
> sizing
> the row each time?

Hpyifur · External Since: Jan 12, 2007 Posts: 2

RD - Thanks for your response.

My problem is that it's an area that needs to have information entered into
by other people (and could vary greatly as to the input). It has to be left
justified and is limited on how wide the merged area can be, that's why I was
looking to use the Wrap Text option.

Thanks
-----------------------------

"RagDyer" wrote:

> *Unmerge* the cells and then use "Center Across Selection" from:
>
> <Format> <Cells. <Alignment> tab,
> Expand the "Horizontal" window and choose "Center Across Selection".
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Hpyifur" <Hpyifur RemoveThis @discussions.microsoft.com> wrote in message
> news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
> > When I merge cells and then choose the wrap text option the Autofit
> > function
> > no longer works. Is there a way to get around this without manually
> > sizing
> > the row each time?
>
>

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

Long audible sigh here.................

One more victim of "merged cells".

Wrap Text works fine on merged cells, but Autofit does not work.

You need VBA code to do that.

Here is code from Greg Wilson.

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

Gord Dibben MS Excel MVP

On Fri, 12 Jan 2007 09:42:00 -0800, Hpyifur <Hpyifur DeleteThis @discussions.microsoft.com>
wrote:

>RD - Thanks for your response.
>
>My problem is that it's an area that needs to have information entered into
>by other people (and could vary greatly as to the input). It has to be left
>justified and is limited on how wide the merged area can be, that's why I was
>looking to use the Wrap Text option.
>
>Thanks
>-----------------------------
>
>"RagDyer" wrote:
>
>> *Unmerge* the cells and then use "Center Across Selection" from:
>>
>> <Format> <Cells. <Alignment> tab,
>> Expand the "Horizontal" window and choose "Center Across Selection".
>> --
>> HTH,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "Hpyifur" <Hpyifur DeleteThis @discussions.microsoft.com> wrote in message
>> news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
>> > When I merge cells and then choose the wrap text option the Autofit
>> > function
>> > no longer works. Is there a way to get around this without manually
>> > sizing
>> > the row each time?
>>
>>

Chris · External Since: Apr 10, 2006 Posts: 375

Gord
I to used you code, works great, however not if I protected document. Any
suggestions?
--
Regards

"Gord Dibben" wrote:

> Long audible sigh here.................
>
> One more victim of "merged cells".
>
> Wrap Text works fine on merged cells, but Autofit does not work.
>
> You need VBA code to do that.
>
> Here is code from Greg Wilson.
>
> 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
>
>
> Gord Dibben MS Excel MVP
>
> On Fri, 12 Jan 2007 09:42:00 -0800, Hpyifur <Hpyifur.TakeThisOut@discussions.microsoft.com>
> wrote:
>
> >RD - Thanks for your response.
> >
> >My problem is that it's an area that needs to have information entered into
> >by other people (and could vary greatly as to the input). It has to be left
> >justified and is limited on how wide the merged area can be, that's why I was
> >looking to use the Wrap Text option.
> >
> >Thanks
> >-----------------------------
> >
> >"RagDyer" wrote:
> >
> >> *Unmerge* the cells and then use "Center Across Selection" from:
> >>
> >> <Format> <Cells. <Alignment> tab,
> >> Expand the "Horizontal" window and choose "Center Across Selection".
> >> --
> >> HTH,
> >>
> >> RD
> >>
> >> ---------------------------------------------------------------------------
> >> Please keep all correspondence within the NewsGroup, so all may benefit !
> >> ---------------------------------------------------------------------------
> >> "Hpyifur" <Hpyifur.TakeThisOut@discussions.microsoft.com> wrote in message
> >> news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
> >> > When I merge cells and then choose the wrap text option the Autofit
> >> > function
> >> > no longer works. Is there a way to get around this without manually
> >> > sizing
> >> > the row each time?
> >>
> >>
>
>

Greg Wilson · External Since: Apr 16, 2006 Posts: 89

In place of the word "password" you would hard code your password and remove
the leading apostrophe assuming it's password protected. Otherwise ignore it.
Minimal testing:-

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
Dim ProtectStatus As Boolean

With Target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect ' "password"
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
On Error Resume Next
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
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect ' "password"
End If
End With
End Sub

Regards,
Greg

"Chris" wrote:

> Gord
> I to used you code, works great, however not if I protected document. Any
> suggestions?
> --
> Regards
>
>
> "Gord Dibben" wrote:
>
> > Long audible sigh here.................
> >
> > One more victim of "merged cells".
> >
> > Wrap Text works fine on merged cells, but Autofit does not work.
> >
> > You need VBA code to do that.
> >
> > Here is code from Greg Wilson.
> >
> > 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
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Fri, 12 Jan 2007 09:42:00 -0800, Hpyifur <Hpyifur DeleteThis @discussions.microsoft.com>
> > wrote:
> >
> > >RD - Thanks for your response.
> > >
> > >My problem is that it's an area that needs to have information entered into
> > >by other people (and could vary greatly as to the input). It has to be left
> > >justified and is limited on how wide the merged area can be, that's why I was
> > >looking to use the Wrap Text option.
> > >
> > >Thanks
> > >-----------------------------
> > >
> > >"RagDyer" wrote:
> > >
> > >> *Unmerge* the cells and then use "Center Across Selection" from:
> > >>
> > >> <Format> <Cells. <Alignment> tab,
> > >> Expand the "Horizontal" window and choose "Center Across Selection".
> > >> --
> > >> HTH,
> > >>
> > >> RD
> > >>
> > >> ---------------------------------------------------------------------------
> > >> Please keep all correspondence within the NewsGroup, so all may benefit !
> > >> ---------------------------------------------------------------------------
> > >> "Hpyifur" <Hpyifur DeleteThis @discussions.microsoft.com> wrote in message
> > >> news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
> > >> > When I merge cells and then choose the wrap text option the Autofit
> > >> > function
> > >> > no longer works. Is there a way to get around this without manually
> > >> > sizing
> > >> > the row each time?
> > >>
> > >>
> >
> >

Odie · External Since: Mar 01, 2007 Posts: 5

Is there any solution for this problem without the use of code?

"Gord Dibben" wrote:

> Long audible sigh here.................
>
> One more victim of "merged cells".
>
> Wrap Text works fine on merged cells, but Autofit does not work.
>
> You need VBA code to do that.
>
> Here is code from Greg Wilson.
>
> 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
>
>
> Gord Dibben MS Excel MVP
>
> On Fri, 12 Jan 2007 09:42:00 -0800, Hpyifur <Hpyifur.RemoveThis@discussions.microsoft.com>
> wrote:
>
> >RD - Thanks for your response.
> >
> >My problem is that it's an area that needs to have information entered into
> >by other people (and could vary greatly as to the input). It has to be left
> >justified and is limited on how wide the merged area can be, that's why I was
> >looking to use the Wrap Text option.
> >
> >Thanks
> >-----------------------------
> >
> >"RagDyer" wrote:
> >
> >> *Unmerge* the cells and then use "Center Across Selection" from:
> >>
> >> <Format> <Cells. <Alignment> tab,
> >> Expand the "Horizontal" window and choose "Center Across Selection".
> >> --
> >> HTH,
> >>
> >> RD
> >>
> >> ---------------------------------------------------------------------------
> >> Please keep all correspondence within the NewsGroup, so all may benefit !
> >> ---------------------------------------------------------------------------
> >> "Hpyifur" <Hpyifur.RemoveThis@discussions.microsoft.com> wrote in message
> >> news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
> >> > When I merge cells and then choose the wrap text option the Autofit
> >> > function
> >> > no longer works. Is there a way to get around this without manually
> >> > sizing
> >> > the row each time?
> >>
> >>
>
>

Dave Peterson · External Since: Jul 08, 2005 Posts: 15610

Adjust the rowheight manually????

Odie wrote:
>
> Is there any solution for this problem without the use of code?
>
> "Gord Dibben" wrote:
>
> > Long audible sigh here.................
> >
> > One more victim of "merged cells".
> >
> > Wrap Text works fine on merged cells, but Autofit does not work.
> >
> > You need VBA code to do that.
> >
> > Here is code from Greg Wilson.
> >
> > 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
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Fri, 12 Jan 2007 09:42:00 -0800, Hpyifur <Hpyifur.TakeThisOut@discussions.microsoft.com>
> > wrote:
> >
> > >RD - Thanks for your response.
> > >
> > >My problem is that it's an area that needs to have information entered into
> > >by other people (and could vary greatly as to the input). It has to be left
> > >justified and is limited on how wide the merged area can be, that's why I was
> > >looking to use the Wrap Text option.
> > >
> > >Thanks
> > >-----------------------------
> > >
> > >"RagDyer" wrote:
> > >
> > >> *Unmerge* the cells and then use "Center Across Selection" from:
> > >>
> > >> <Format> <Cells. <Alignment> tab,
> > >> Expand the "Horizontal" window and choose "Center Across Selection".
> > >> --
> > >> HTH,
> > >>
> > >> RD
> > >>
> > >> ---------------------------------------------------------------------------
> > >> Please keep all correspondence within the NewsGroup, so all may benefit !
> > >> ---------------------------------------------------------------------------
> > >> "Hpyifur" <Hpyifur.TakeThisOut@discussions.microsoft.com> wrote in message
> > >> news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
> > >> > When I merge cells and then choose the wrap text option the Autofit
> > >> > function
> > >> > no longer works. Is there a way to get around this without manually
> > >> > sizing
> > >> > the row each time?
> > >>
> > >>
> >
> >

--

Dave Peterson

Odie · External Since: Mar 01, 2007 Posts: 5

Dave, thanks. I guess I should have been more specific.

Is there anyway to make the autofit work with merged cells (other than using
code)? I have a form that is used over and over, so it's a pain to keep
manually adjusting (plus the screen view is different than print view, so it
means lots of switching back and forth).

Another work around that doesn't solve the underlying problem is to redesign
the form.

"Dave Peterson" wrote:

> Adjust the rowheight manually????
>
> Odie wrote:
> >
> > Is there any solution for this problem without the use of code?
> >
> > "Gord Dibben" wrote:
> >
> > > Long audible sigh here.................
> > >
> > > One more victim of "merged cells".
> > >
> > > Wrap Text works fine on merged cells, but Autofit does not work.
> > >
> > > You need VBA code to do that.
> > >
> > > Here is code from Greg Wilson.
> > >
> > > 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
> > >
> > >
> > > Gord Dibben MS Excel MVP
> > >
> > > On Fri, 12 Jan 2007 09:42:00 -0800, Hpyifur <Hpyifur.RemoveThis@discussions.microsoft.com>
> > > wrote:
> > >
> > > >RD - Thanks for your response.
> > > >
> > > >My problem is that it's an area that needs to have information entered into
> > > >by other people (and could vary greatly as to the input). It has to be left
> > > >justified and is limited on how wide the merged area can be, that's why I was
> > > >looking to use the Wrap Text option.
> > > >
> > > >Thanks
> > > >-----------------------------
> > > >
> > > >"RagDyer" wrote:
> > > >
> > > >> *Unmerge* the cells and then use "Center Across Selection" from:
> > > >>
> > > >> <Format> <Cells. <Alignment> tab,
> > > >> Expand the "Horizontal" window and choose "Center Across Selection".
> > > >> --
> > > >> HTH,
> > > >>
> > > >> RD
> > > >>
> > > >> ---------------------------------------------------------------------------
> > > >> Please keep all correspondence within the NewsGroup, so all may benefit !
> > > >> ---------------------------------------------------------------------------
> > > >> "Hpyifur" <Hpyifur.RemoveThis@discussions.microsoft.com> wrote in message
> > > >> news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
> > > >> > When I merge cells and then choose the wrap text option the Autofit
> > > >> > function
> > > >> > no longer works. Is there a way to get around this without manually
> > > >> > sizing
> > > >> > the row each time?
> > > >>
> > > >>
> > >
> > >
>
> --
>
> Dave Peterson
>

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

You were quite specific as was Dave's answer.

Without code you must manually adjust the heights.

Blow away those merged cells and forget that feature exists to make life much
simpler.

Gord Dibben MS Excel MVP

On Thu, 28 Jun 2007 09:02:02 -0700, Odie <Odie.TakeThisOut@discussions.microsoft.com> wrote:

>Dave, thanks. I guess I should have been more specific.
>
>Is there anyway to make the autofit work with merged cells (other than using
>code)? I have a form that is used over and over, so it's a pain to keep
>manually adjusting (plus the screen view is different than print view, so it
>means lots of switching back and forth).
>
>Another work around that doesn't solve the underlying problem is to redesign
>the form.
>
>"Dave Peterson" wrote:
>
>> Adjust the rowheight manually????
>>
>> Odie wrote:
>> >
>> > Is there any solution for this problem without the use of code?
>> >
>> > "Gord Dibben" wrote:
>> >
>> > > Long audible sigh here.................
>> > >
>> > > One more victim of "merged cells".
>> > >
>> > > Wrap Text works fine on merged cells, but Autofit does not work.
>> > >
>> > > You need VBA code to do that.
>> > >
>> > > Here is code from Greg Wilson.
>> > >
>> > > 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
>> > >
>> > >
>> > > Gord Dibben MS Excel MVP
>> > >
>> > > On Fri, 12 Jan 2007 09:42:00 -0800, Hpyifur <Hpyifur.TakeThisOut@discussions.microsoft.com>
>> > > wrote:
>> > >
>> > > >RD - Thanks for your response.
>> > > >
>> > > >My problem is that it's an area that needs to have information entered into
>> > > >by other people (and could vary greatly as to the input). It has to be left
>> > > >justified and is limited on how wide the merged area can be, that's why I was
>> > > >looking to use the Wrap Text option.
>> > > >
>> > > >Thanks
>> > > >-----------------------------
>> > > >
>> > > >"RagDyer" wrote:
>> > > >
>> > > >> *Unmerge* the cells and then use "Center Across Selection" from:
>> > > >>
>> > > >> <Format> <Cells. <Alignment> tab,
>> > > >> Expand the "Horizontal" window and choose "Center Across Selection".
>> > > >> --
>> > > >> HTH,
>> > > >>
>> > > >> RD
>> > > >>
>> > > >> ---------------------------------------------------------------------------
>> > > >> Please keep all correspondence within the NewsGroup, so all may benefit !
>> > > >> ---------------------------------------------------------------------------
>> > > >> "Hpyifur" <Hpyifur.TakeThisOut@discussions.microsoft.com> wrote in message
>> > > >> news:0518FECC-94CC-40B2-ACEF-1919895A5399@microsoft.com...
>> > > >> > When I merge cells and then choose the wrap text option the Autofit
>> > > >> > function
>> > > >> > no longer works. Is there a way to get around this without manually
>> > > >> > sizing
>> > > >> > the row each time?
>> > > >>
>> > > >>
>> > >
>> > >
>>
>> --
>>
>> Dave Peterson
>>

bf · Joined: Oct 25, 2007 Posts: 1

Thanks for the info on the code. I am having trouble to get the code to work properly, any ideas? I pasted it into the worksheet where I need the autofit code to work, but with no success.

What triggers the code to work? The merging of the cells, the autofit process???