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
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 [Login to view extended thread Info.]
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 [Login to view extended thread Info.]
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 [Login to view extended thread Info.]
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 [Login to view extended thread Info.]
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 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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
> > 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
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 [Login to view extended thread Info.]
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 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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

--

Dave Peterson
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 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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
> > > 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?
> > > >>
> > > >>
> > >
> > >
>
> --
>
> Dave Peterson
>
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 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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 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
>> > > 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?
>> > > >>
>> > > >>
>> > >
>> > >
>>
>> --
>>
>> Dave Peterson
>>
Back to top
bf



Joined: Oct 25, 2007
Posts: 1



PostPosted: Thu Oct 25, 2007 3:34 pm    Post subject: Worksheet code [Login to view extended thread Info.]

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 [Login to view extended thread Info.]
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 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

The cell I use the code in locks after i enter text and go to the next cell;
I'm unable to access cell much less re-enter text after I type once and go to
another cell. I'm using excel 2007 and don't use a password when protecting
sheet. After I click "Protect Sheet", click ok without entering password,
enter text into cell, I'm prompted with something about a expanind width, I
click "yes" again, but when I try to go back to cell I can't because it
become locked. How can I protect sheet, use "text wrap in merge cell" code,
enter data, expand merged cells accordingly, not have the cell lock after I
enter text, and be able to re-enter data whenever?

"Greg Wilson" wrote:

> 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
> > > 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
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 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I am new to Visual Basic.

I pasted the code into the box that appeared when I selected "View Code."
However, when I returned to the Worksheet I still could not autofit (either
by double-clicking the row or by selecting "Autofit").

How may I activate the code?

I tried (1) "Save" and (2) "Run" ... which created a Macro that runs when I
open the document but I cannot locate the Macro to use / delete it.

The Procedure is currently set to "Change." I wondered if that was a
problem since this is "Event" activated ...

Thank you for your patience!
Erin

"Gord Dibben" wrote:

> The code is event code and runs upon entry of text to merged cells.
>
> Right-clcik on the sheet tab and "View Code"
>
> Copy/paste the code into that sheet module.
>
>
> Gord Dibben MS Excel MVP
>
> On Mon, 1 Oct 2007 15:07:01 -0700, Using code in Excel <Using code in
> Excel@discussions.microsoft.com> wrote:
>
> >How is this code actually used?
> >
> >"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: Thu Sep 17, 2009 6:23 pm    Post subject: Re: Merged cells won't Autofit row height [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

When you right-click on the sheet tab and "View Code" a sheet module(not a
"box") will open.

Paste the code into that module.

The rows must be preset to WrapText and Autofit.

You do not "run" the code nor do you double-click on a row

The "change" event takes place when you type something into a merged cell
and hit the ENTER key.

At that point the code is "activated" and your row expands to fit.


Gord


On Thu, 17 Sep 2009 11:23:16 -0700, ELizzy010
wrote:

>I am new to Visual Basic.
>
>I pasted the code into the box that appeared when I selected "View Code."
>However, when I returned to the Worksheet I still could not autofit (either
>by double-clicking the row or by selecting "Autofit").
>
>How may I activate the code?
>
>I tried (1) "Save" and (2) "Run" ... which created a Macro that runs when I
>open the document but I cannot locate the Macro to use / delete it.
>
>The Procedure is currently set to "Change." I wondered if that was a
>problem since this is "Event" activated ...
>
>Thank you for your patience!
>Erin
>
>"Gord Dibben" wrote:
>
>> The code is event code and runs upon entry of text to merged cells.
>>
>> Right-clcik on the sheet tab and "View Code"
>>
>> Copy/paste the code into that sheet module.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Mon, 1 Oct 2007 15:07:01 -0700, Using code in Excel <Using code in
>> Excel@discussions.microsoft.com> wrote:
>>
>> >How is this code actually used?
>> >
>> >"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
leigo



Joined: Nov 04, 2009
Posts: 1



PostPosted: Wed Nov 04, 2009 8:41 pm    Post subject: VB code poses problem for undo feature [Login to view extended thread Info.]

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: [Login to view extended thread Info.]

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 [Login to view extended thread Info.]
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 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

The configuation of the merged cells is not your problem.

Works for me with A1:B2 merged.......also A5:D9

Wrap text and row autofit have to be pre-set.

Back to your error message.................

You will receive that particular error message when the worksheet is
protected.

Maybe you want to unprotect then re-protect?

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
On Error GoTo endall
Me.Unprotect Password:="justme"
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
endall:
Me.Protect Password:="justme"
End Sub


Gord


On Mon, 11 Jan 2010 20:38:01 -0800, Tommy-ID
wrote:

>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
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 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

On Tuesday, January 12, 2010 5:01:53 PM UTC-5, Gord Dibben wrote:
> The configuation of the merged cells is not your problem.
>
> Works for me with A1:B2 merged.......also A5:D9
>
> Wrap text and row autofit have to be pre-set.
>
> Back to your error message.................
>
> You will receive that particular error message when the worksheet is
> protected.
>
> Maybe you want to unprotect then re-protect?
>
> 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
> On Error GoTo endall
> Me.Unprotect Password:="justme"
> 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
> endall:
> Me.Protect Password:="justme"
> End Sub
>
>
> Gord
>
>
> On Mon, 11 Jan 2010 20:38:01 -0800, Tommy-ID
> wrote:
>
> >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?
> >> >>
> >> >>
> >>
> >>

Gord, it has been a while since you wrote this, but I'm going to shoot this out into the ether in the hopes that it will reach you. Is there any way to make this so that it doesn't empty the undo cache? I understand that the workbook beforeprint event does something like what I'm looking for, but don't know how to change your code so that it works there.

Thank you in advance,
Pat
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions All times are: Eastern Time (US & Canada)
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