Private Sub AutosizeMergedCells()
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim r2 As Range
Dim cell As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
If ActiveSheet.Name <> "amform" Then
MsgBox "You can only run this process when you are on the EVALUATION FORM. This process has been canceled."
Exit Sub
End If
Set ln1 = Worksheets("amform")
Set ln1a1 = ln1.Range("A20")
Set ln1a2 = ln1a1
Application.ScreenUpdating = False
Oblean = True
ln1.Unprotect Password:="lplains"
Do While Left(ln1a1.Offset(0, 1), 2) <> "B."
Set ln1a1 = ln1a1.Offset(1)
Loop
Set ln1a3 = ln1.Range(ln1a2.Offset(, 1), ln1a1.Offset(-2, 15))
Set ln1a2 = ln1a1
Do While Left(ln1a1.Offset(0, 1), 2) <> "C."
Set ln1a1 = ln1a1.Offset(1)
Loop
Set ln1a4 = ln1.Range(ln1a2.Offset(2, 1), ln1a1.Offset(-2, 15))
Set ln1a2 = ln1a1
Do While Left(ln1a1.Offset(0, 1), 2) <> "D."
Set ln1a1 = ln1a1.Offset(1)
Loop
Set ln1a5 = ln1.Range(ln1a2.Offset(2, 1), ln1a1.Offset(-2, 15))
Set ln1a2 = ln1a1
Do While Left(ln1a1.Offset(0, 1), 2) <> "E."
Set ln1a1 = ln1a1.Offset(1)
Loop
Set ln1a2 = ln1a1
Do While Left(ln1a1.Offset(0, 1), 2) <> "F."
Set ln1a1 = ln1a1.Offset(1)
Loop
Set ln1a6 = ln1.Range(ln1a2.Offset(2, 1), ln1a1.Offset(-2, 15))
For Each cell In Union(ln1a3, ln1a4, ln1a5, ln1a6)
'On Error Resume Next
If cell.MergeCells Then
Set r2 = cell.MergeArea
If cell.Address = r2(1).Address Then
If Len(Trim(cell.Value)) <> 0 Then
With cell
If .MergeCells And .Text Then
Set c = cell.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
End If
End With
End If
End If
End If
Next cell
ln1.Protect Password:="lplains"
Application.ScreenUpdating = True
End Sub
Thank you very much!!!!
Bookmarks