Hi! Thank you so much to everyone on all of these forums! I've amazed myself with what I've been able to do with the help of Google and all the great people that reply to these posts. I'm almost complete with a project that I'm working and stuck on one thing. I have some merged cells with wrapped text and found that the row height wasn't expanding with my text. I found some code that pretty much solved my problem; however, it's not quite perfect as it's adding space for an additional row when it isn't necessary. For example, in one of my merged cells, the text could all fit on one row with about 40 characters to spare before it is necessary to add another row in, but it's still adding the extra row space, making the report look unprofessional. I'm looking at the code that I found, but don't see anywhere that I can experiment with tweaking it to perfect it. I'm hoping that someone out there can help. I'm a first-time poster and hopefully I don't mess up how I'm supposed to post the code. Any suggestions for modifications so that I get rid of the extra, unnecessary blank lines, are greatly appreciated!
Code:
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 ""
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 ""
End If
End With
End Sub
