Autosize merged cells

[Teri]

I have a merged cell that should auto-size as users enter data. I understand
that VBA code is necessary and I have even attempted to use the following
which doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
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

Can anyone help please?

 

[FSt1]

hi
after this line....
Private Sub Worksheet_Change(ByVal Target As Range)
you have an end sub. delete it. you are ending the code before it starts.
also make sure word wrap in on. cell should expand after pressing enter.

regards
FSt1

 

[Gord Dibben]

Teri

The code works fine if all the rules are followed.

1. Cells are pre-set to wraptext.

2. Rows are pre-set to Autofit.

3. Code goes into the worksheet module

Where are you having trouble? Other than using of merged cells which I believe
is the very worst feature Excel Developers ever introduced.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Good catch FSt1


Gord

hi
after this line....
Private Sub Worksheet_Change(ByVal Target As Range)
you have an end sub. delete it. you are ending the code before it starts.
also make sure word wrap in on. cell should expand after pressing enter.

regards
FSt1

 

[Gord Dibben]

Or 4. if the code is correct<g>

Missed that extra End Sub

See FSt1's post.


Gord

 

[Teri]

Sorry, still couldn't get it to work. I think I'm going to use Word.

 

[Gord Dibben]

That would be my second or third choice.

First choice.......DO NOT USE MERGED CELLS!!


Gord

 

[LyleB_Austin]

Okay, I've read the posts about auto sizing merged cells, copied the code,
and the merged cells are resizing, but...

Once I've entered data into the merged field and moved on to the next, I
can't go back and alter the previous merged field.

In case it matters, this is in a protected document. The merged cells are
unlocked. I'm doing this to force users to navigate through the form the
right way.