Issues with wrapping Excel text macro
 

Dear Excel VBA Gurus,

During a previous post to your site, I was provided with some code (see
below) to assist in automatically resizing a cell height in a workbook
template even if it has been merged:

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

Initially, this code seemed to fix the issue. However, whilst testing it
extensively lately, we have noticed that if you reduce the amount of text in
one cell (eg. a cell in Column B gets changed so it only needs the row height
to fit one line of text), then column A is automatically resized to the same
row height size (even though it might need a row height of two lines to
display all the text - and hence text is hidden). Although this can be fixed
manually by a user clicking in column A, making a change and then clicking
out, this is not ideal for an organisational template.

Does anyone have any suggestions on how we could improve the code above to
alleviate this issue?

Any assistance would be gratefully received.

Regards
JD2