Auto Row Height containing merged cells

[Hart]

Hi board members:
I have a sheet with several hundred rows containing merged cells. I need to be able to adjust the row height to accommodate the text in merged cells (column B through column J) if column A contains the word "Comment". Any help would be greatly appreciated.

 

[Fluff]

Easiest option, remove the merged cells, they are an abomination & will only cause you pain & grief.

If you are unwilling to do that, look here AutoFit Merged Cell Row Height - Contextures Blog

 

[DanteAmor]

Run the "Auto_Row_Height" macro on your sheet:

Sub Auto_Row_Height()
  Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
      If Cells(i, "A") = "Comment" Then
        Call Adjust_Row(Range("B" & i & ":J" & i))
      End If
    Next
    Application.ScreenUpdating = True
End Sub
'
Sub Adjust_Row(rngRango As Range)
  Dim n, sngAnchoTotal, sngAnchoCelda, sngAlto
    For n = 1 To rngRango.Columns.Count
        sngAnchoTotal = sngAnchoTotal + rngRango.Cells(1, n).ColumnWidth
    Next n
    With rngRango.Cells(1, 1)
        sngAnchoCelda = .ColumnWidth
        .MergeCells = False
        .ColumnWidth = sngAnchoTotal
        .WrapText = True
        rngRango.Parent.Rows(rngRango.Row).AutoFit
        sngAlto = .RowHeight
    End With
    With rngRango
        .Merge
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .Columns(1).EntireColumn.ColumnWidth = sngAnchoCelda
        .Columns(1).RowHeight = sngAlto
    End With
End Sub

 

[Hart]

Hi Dante and thanks so much! This works perfectly with the exception that cell A1 need only contain the word "Comment" as opposed to ="Comment". "Comment" will always be the last seven characters in cell A1.

 

[DanteAmor]

Change this line:

If Right(Cells(i, "A"), 7) = "Comment" Then

 

[Hart]

Thanks Dante - works perfectly!

 

[DanteAmor]

Im glad to help you, thanks for the feedback

 

[Hart]

The vba code works fine on my version - 2016 but not on 2010. Any suggestions? There is an extra line added to the line height and other cells have been widened - not sure what to think.