Excel: When Cell Height is 409.5, create & merge with new Row

[Dudder]

Hi there!
New to VBA and I'm trying to figure this out. This is an issue I need to automate.

Sometimes I have too much text in a cell, and it doesn't fit inside the max Cell Height of 409.5. I'd like to run a script that identifies when a cell's height is 409.5, inserts a row below, merges those two rows, and then Autofits the height. The goal is that all of my text should be visible in the cell.

Any help would be appreciated, thank you very much!

 

[Dudder]

Thanks Andrzejek, but that isn't helpful. Would appreciate anyone else's input to this question. It seems to be a common issue for excel users.

 

[SkipVought]

AutoFit will not work in merged cells.

This is how to get the merge to work, though...

Private Sub Worksheet_Change(ByVal Target As Range)
    
    With Target
        .EntireRow.AutoFit
        
        If .MergeCells Then
            'cell is already merged
        Else
            If .RowHeight > 409.5 Then
                Application.EnableEvents = False
                .Offset(-1, 0).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                Range(.Cells(1, 1), .Cells(2, 1)).Merge
                Application.EnableEvents = True
            End If
            [b]'AutoFit will not function with merged cells
[/b]            Rows.AutoFit
        End If
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

>AutoFit will not work in merged cells.

Indeed not - but you can write your own with the use of a richtext control and EM_FORMATTEXT