Auto fit in merged cells

Perhaps this isn't your problem, but just to be sure:
Assuming you are using Jim Rech's code, you have to run the code for it to
do anything. Just putting it in a module does not cause it to autofit
merged cells.

Also, this line
If .Rows.Count = 1 And .WrapText = True Then
tells you that it only works for single row, multiple column merged cells
(with WrapText set to true)

--
regards,
Tom Ogilvy



"LDF" <LDF@discussions.microsoft.com> wrote in message
news:BD860195-C9A1-4AA3-AAA9-E49979477AB9@microsoft.com...
> I am trying to paste in the code to ensure the merged cells in my
spreadsheet
> autofit the text that is written.
> I have followed previous queries on the subject, and used the code
provided.
> Never having attempted this before, of course it isn't working and I don't
> know why.
> Any help would be appreciated

Is there any reason why this code would work fine in Excel 2003 but not in
Excel 2000?

Thanks very much,
Joe

Dim OldRng As Range ' DECLARED THIS AT TOP OF MODULE

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 Protected As Boolean

If Target.Column = 1 And (Target.Row > 16 And Target.Row < 42) Then
Cells(Target.Row, 2).Select
End If
Protected = False
Set c = Cells(Target.Row, 1)
If OldRng Is Nothing Then Set OldRng = c
If Not Intersect(OldRng, c) Is Nothing Then
Application.ScreenUpdating = True
If Me.ProtectContents Then
Protected = True
Me.Unprotect
End If
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
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
ma.Locked = False
If Protected Then Me.Protect
Application.ScreenUpdating = True
End If
Set OldRng = Target
Set c = Nothing
Set OldRng = Nothing
End Sub

"Tom Ogilvy" wrote:

> Perhaps this isn't your problem, but just to be sure:
> Assuming you are using Jim Rech's code, you have to run the code for it to
> do anything. Just putting it in a module does not cause it to autofit
> merged cells.
>
> Also, this line
> If .Rows.Count = 1 And .WrapText = True Then
> tells you that it only works for single row, multiple column merged cells
> (with WrapText set to true)
>
> --
> regards,
> Tom Ogilvy
>
>
>
> "LDF" <LDF@discussions.microsoft.com> wrote in message
> news:BD860195-C9A1-4AA3-AAA9-E49979477AB9@microsoft.com...
> > I am trying to paste in the code to ensure the merged cells in my
> spreadsheet
> > autofit the text that is written.
> > I have followed previous queries on the subject, and used the code
> provided.
> > Never having attempted this before, of course it isn't working and I don't
> > know why.
> > Any help would be appreciated
>
>
>

It was originally written in the xl97/xl2000 timeframe, so I don't see any
reason it would not work in all versions after xl95 (merged cells were
introduced in xl97).

--
Regards,
Tom Ogilvy


"Joe" <Joe@discussions.microsoft.com> wrote in message
news:6E3ED7DD-DF74-496F-B624-B4EFACACD5C9@microsoft.com...
> Is there any reason why this code would work fine in Excel 2003 but not in
> Excel 2000?
>
> Thanks very much,
> Joe
>
> Dim OldRng As Range ' DECLARED THIS AT TOP OF MODULE
>
> 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 Protected As Boolean
>
> If Target.Column = 1 And (Target.Row > 16 And Target.Row < 42) Then
> Cells(Target.Row, 2).Select
> End If
> Protected = False
> Set c = Cells(Target.Row, 1)
> If OldRng Is Nothing Then Set OldRng = c
> If Not Intersect(OldRng, c) Is Nothing Then
> Application.ScreenUpdating = True
> If Me.ProtectContents Then
> Protected = True
> Me.Unprotect
> End If
> cWdth = c.ColumnWidth
> Set ma = c.MergeArea
> For Each cc In ma.Cells
> MrgeWdth = MrgeWdth + cc.ColumnWidth
> 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
> ma.Locked = False
> If Protected Then Me.Protect
> Application.ScreenUpdating = True
> End If
> Set OldRng = Target
> Set c = Nothing
> Set OldRng = Nothing
> End Sub
>
> "Tom Ogilvy" wrote:
>
> > Perhaps this isn't your problem, but just to be sure:
> > Assuming you are using Jim Rech's code, you have to run the code for it
to
> > do anything. Just putting it in a module does not cause it to autofit
> > merged cells.
> >
> > Also, this line
> > If .Rows.Count = 1 And .WrapText = True Then
> > tells you that it only works for single row, multiple column merged
cells
> > (with WrapText set to true)
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> >
> >
> > "LDF" <LDF@discussions.microsoft.com> wrote in message
> > news:BD860195-C9A1-4AA3-AAA9-E49979477AB9@microsoft.com...
> > > I am trying to paste in the code to ensure the merged cells in my
> > spreadsheet
> > > autofit the text that is written.
> > > I have followed previous queries on the subject, and used the code
> > provided.
> > > Never having attempted this before, of course it isn't working and I
don't
> > > know why.
> > > Any help would be appreciated
> >
> >
> >