Help!

Merged cells won't Autofit row height

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions RSS
Next:  Using 2 IF statements for the same cell  
Author Message
Hpyifur
External


Since: Jan 12, 2007
Posts: 2



PostPosted: Fri Jan 12, 2007 2:10 pm    Post subject: Merged cells won't Autofit row height
Archived from groups: microsoft>public>excel>misc (more info?)

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?
Back to top
Advertisement
RagDyer
External


Since: Jun 14, 2004
Posts: 762



PostPosted: Fri Jan 12, 2007 2:24 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

*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" wrote in message

> 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?
Back to top
Hpyifur
External


Since: Jan 12, 2007
Posts: 2



PostPosted: Fri Jan 12, 2007 2:42 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

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" wrote in message
>
> > 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?
>
>
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Fri Jan 12, 2007 3:38 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

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
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" wrote in message
>>
>> > 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?
>>
>>
Back to top
Chris
External


Since: Apr 10, 2006
Posts: 403



PostPosted: Mon Mar 05, 2007 3:20 am    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

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
> 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" wrote in message
> >>
> >> > 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?
> >>
> >>
>
>
Back to top
Greg Wilson
External


Since: Apr 16, 2006
Posts: 89



PostPosted: Mon Mar 05, 2007 4:16 am    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
Odie
External


Since: Mar 01, 2007
Posts: 5



PostPosted: Thu Jun 28, 2007 12:02 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

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
> 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" wrote in message
> >>
> >> > 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?
> >>
> >>
>
>
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Thu Jun 28, 2007 2:26 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
Odie
External


Since: Mar 01, 2007
Posts: 5



PostPosted: Thu Jun 28, 2007 2:26 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Thu Jun 28, 2007 2:26 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
bf



Joined: Oct 25, 2007
Posts: 1



PostPosted: Thu Oct 25, 2007 3:34 pm    Post subject: Worksheet code

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???
Back to top
mooresk257
External


Since: May 03, 2009
Posts: 1



PostPosted: Sun May 03, 2009 7:16 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: microsoft>public>excel>misc (more info?)

In Excel 2007, this code also changes the cell property from "unlocked" to
"locked". I think I'll see how I can restructure my sheet to avoid merging
cells.

"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
> 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" wrote in message
> >>
> >> > 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?
> >>
> >>
>
>
Back to top
Jeno
External


Since: Dec 29, 2006
Posts: 3



PostPosted: Mon Jul 20, 2009 6:43 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
ELizzy010
External


Since: Sep 17, 2009
Posts: 1



PostPosted: Thu Sep 17, 2009 3:23 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Thu Sep 17, 2009 6:23 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
leigo



Joined: Nov 04, 2009
Posts: 1



PostPosted: Wed Nov 04, 2009 8:41 pm    Post subject: VB code poses problem for undo feature

The code posted by Gord Dibben is great for solving the row height autofit problem in merged cells, but I have found that once the code is in place I am no longer able to use the undo feature on the affected cells.

Does anyone have a solution to this problem?

Thanks
Back to top
amber01



Joined: Nov 23, 2009
Posts: 1



PostPosted: Mon Nov 23, 2009 6:18 pm    Post subject:

Hi,

When you protect your worksheet there are some options to allow for the user. One of the options is to format rows. So you can autofit rows when protecting your sheet.
Back to top
Tommy-ID
External


Since: Jan 11, 2010
Posts: 1



PostPosted: Tue Jan 12, 2010 1:38 am    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: microsoft>public>excel>misc (more info?)

Dear Gord Dibben,

Looks very helpfull thanks. Anyway, seems like it only work with merged
cells that merged the on one row (e.g. A1:A2). I try to merge A1:B2 or
multiple row and the error shows: Unable to set the ColumnWidth property of
the Range class. Is there any way to solve this?

Thanks.

Tommy-ID

"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
> 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" wrote in message
> >>
> >> > 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?
> >>
> >>
>
>
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Tue Jan 12, 2010 7:01 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
pathook
External


Since: Nov 09, 2013
Posts: 1



PostPosted: Sat Nov 09, 2013 4:25 pm    Post subject: Re: Merged cells won't Autofit row height
Archived from groups: per prev. post (more info?)

Back to top
Advertisement
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum