Results 1 to 3 of 3

Thread: Modifying code that autofits the row height of merged cells

  1. #1

    Modifying code that autofits the row height of merged cells

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi I'm using the code below on a couple different worksheets and have found that, on one sheet where the merged columns total 1,146 pixels, Excel limits the # of rows to 6, i.e. if you enter more content than would be visible in 6 rows, it's not displayed. On another sheet where the merged columns total 815 pixels, a limit still exists, but now it's 8 rows.

    Does anybody know any way around this? The fields that I am using this for on the sheet with 1,146 pixels really needs to display at least 10 rows if the user enters that much content.


    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim MergeWidth As Single
    Dim cM As Range
    Dim AutoFitRng As Range
    Dim CWidth As Double
    Dim NewRowHt As Double
    Dim str01 As String
    str01 = "LossEval"
    If Not Intersect(Target, Range(str01)) Is Nothing Then
    Application.ScreenUpdating = False
    On Error Resume Next
    Set AutoFitRng = Range(Range(str01).MergeArea.Address)
    With AutoFitRng
    .MergeCells = False
    CWidth = .Cells(1).ColumnWidth
    MergeWidth = 0
    For Each cM In AutoFitRng
    cM.WrapText = True
    MergeWidth = cM.ColumnWidth + MergeWidth
    'small adjustment to temporary width
    MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66
    .Cells(1).ColumnWidth = MergeWidth
    NewRowHt = Application.Max(.RowHeight, 15)
    .Cells(1).ColumnWidth = CWidth
    .MergeCells = True
    .RowHeight = NewRowHt
    End With
    Application.ScreenUpdating = True
    End If
    End Sub

  2. #2
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Excel Version
    can you attach a sample file ?

  3. #3
    Hi Patel, sample file attached. This is the worksheet that allows up to 8 rows.


    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts