Autosize merged cells

  • Thread starter Thread starter Teri
  • Start date Start date
T

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?
 
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
 
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
 
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
Click to expand...
 
That would be my second or third choice.

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


Gord
 
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.
 
Back
Top