Afternoon,
I have a spreadsheet with rows 20 - 34 having two merged columns B-E and F-J (I know nobody likes merged cells, but i need it to be this way as the people who are using the spreadsheets are proper novices and therefore rubbish at entering information correctly.)
I have managed to cobble together a macro that auto adjusts the height of the columns to auto fit the text.
This works by comparing the merged active cell text length LEN with the other in the same row and if it has more text then adjusts the height to fit. See the code that compares and trigger the actual resizing code below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B20:F34" '<<<< change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Dim LenValue As Integer
Dim LenValuenext As Integer
With Target
'rhods bit
LenValue = Len(ActiveCell)
LenValuenext = Len(ActiveCell.Offset(0, 1))
If LenValue > LenValuenext Then
Call height
Else: GoTo deadwood
deadwood:
End If
'end of rhod's bit
End With
ws_exit:
End If
Application.EnableEvents = True
End Sub
and that triggers the code to actually do it.
Sub height()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
'.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
.RowHeight = PossNewRowHeight + 3
End If
End With
End If
End Sub
to get it to work I am having to trigger it from the worksheet_selectionchange. This only works when you click on the cell and not when you add/remove text.
I have tried triggering from worksheet_change, but absolutly nothing happens (not when you type, not when you click.)
does anyone know why and how to fix it? This is sending me absolutly mental.
Thanks in advance.
Bookmarks