Auto Row Height

[ExcelRoy]

Hi Guys,

I have the unfixable problem of needing auto row height to merged cells

I have the following code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 16 Then
ActiveSheet.Rows(16).AutoFit
End If
End Sub

Which applies the auto row height to row 16, but how would i do the same for rows 16 thru 216

Thanks for any help

 

[Jerry Sullivan]

Hi Roy,

It sounds like you are already aware that merged cells can be problematic for VBA and yield unexpected results. Autofit is one example of a function that doesn't work well on merged cells.

That being said, you can modify your code so that it will try to AutoFit Rows 16:216 any time there is a change to a value in Rows 16:216 like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Rows("16:216")) Is Nothing Then
        Rows("16:216").AutoFit
    End If
End Sub

 

[ExcelRoy]

Fantastic, Thanks, works perfectly