+ Reply to Thread
Results 1 to 2 of 2

auto fit merged cells

Hybrid View

  1. #1
    Registered User
    Join Date
    12-27-2013
    Location
    Oklahoma City
    MS-Off Ver
    Excel 2003
    Posts
    49

    auto fit merged cells

    is it possible to auto fit the height of merged cells? I need a Comment section that can grow as needed for long tet

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: auto fit merged cells

    select cells and run
    Sub AutoFitRowsHeight()
    Dim j&, q&, f&, l&, p&(3), cWh!, rHh!, i() As Box, cl() As Single, x As Object, objRange As Object
    Application.ScreenUpdating = False
    Set objRange = Selection
    On Error Resume Next
    With ActiveSheet
        For Each x In objRange.Areas
            Set x = IIf(x.Address = .Rows.Address Or x.Address = .Columns(x.Column).Address, .UsedRange, x): p(0) = x.Column
            p(1) = p(0) + x.Columns.Count - 1: p(2) = x.Row: p(3) = p(2) + x.Rows.Count - 1: ReDim cl(p(0) To p(1)): ReDim i(p(2) To p(3))
            For j = p(0) To p(1): cl(j) = .Columns(j).ColumnWidth: Next
            For j = p(3) To p(2) Step -1
                Set x = .Rows(j): i(j).Hdn = x.Hidden: x.AutoFit: i(j).Hght = x.RowHeight
                For l = p(0) To p(1)
                    If .Cells(j, l).MergeCells Then
                        With .Cells(j, l).MergeArea
                            If ActiveSheet.Cells(j, l).Address = .Item(1).Address Then
                                For q = l To l + .Columns.Count - 1: cWh = cWh + cl(q) + 0.647: Next
                                If cWh > 255 Then cWh = 0: GoTo L1
                                For q = j To j + .Rows.Count - 1
                                    If Not i(q).Hdn Then rHh = rHh + i(q).Hght: If f = 0 Then f = q
                                Next
                                .UnMerge: .Item(1).ColumnWidth = cWh: x.AutoFit: rHh = x.RowHeight - (rHh - i(f).Hght)
                                If f <> j Then If i(f).Hght < rHh Then .Rows(f - j + 1).RowHeight = rHh
                                .Merge: .Item(1).ColumnWidth = cl(l): l = l + .Columns.Count - 1
                                If i(f).Hght < rHh Then i(f).Hght = rHh
                                cWh = 0: rHh = 0: f = 0
                            End If
                        End With
                    End If
    L1:        Next
                If i(j).Hght > 0 Then x.RowHeight = i(j).Hght
                If i(j).Hdn Then x.Hidden = True
            Next
        Next
    End With
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto lock cells with a range that includes merged cells.
    By garyreid81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2014, 05:23 AM
  2. Replies: 0
    Last Post: 11-25-2014, 07:08 AM
  3. Auto-expand merged cells
    By amyb2008 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-03-2013, 01:09 PM
  4. Auto fit in merged cells
    By LDF in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-20-2005, 02:45 PM
  5. How do I auto fit when the cells are merged together?
    By Christa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-01-2005, 04:09 PM
  6. Auto Row Height for Merged Cells
    By Jamie in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-30-2005, 08:05 PM
  7. [SOLVED] Auto fit wrapped and merged cells
    By elmo2 in forum Excel General
    Replies: 1
    Last Post: 01-17-2005, 03:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1