mistersend
New Member
- Joined
- May 29, 2020
- Messages
- 15
- Office Version
- 2019
- Platform
- Windows
I've find the following code which can autofit any protected/unprotected row with merged cells and text wrap on.
(VBA is taken from Auto adjust row height (merged cells))
Requirement:
I want to escape some merged cells from autofit even if the wrap text is on for these cells.
Alternatively,
The code should be applied to some specific range only, not in the whole sheet.
Please help if you can...I'll be thankful!!
(VBA is taken from Auto adjust row height (merged cells))
VBA 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 "password"
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 "password"
End If
End With
End Sub
Requirement:
I want to escape some merged cells from autofit even if the wrap text is on for these cells.
Alternatively,
The code should be applied to some specific range only, not in the whole sheet.
Please help if you can...I'll be thankful!!