Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Automatically Change Row Height with Merged Cells

0

Hello,

Is there a way to autofit Row Height with merged cells?

Thanks

Answer
Discuss

Answers

0
Selected Answer

Hi RWSM

I'm guessing that you have several (or many) merged cells in your file and want to autofit all of them.

You can simpy autofit the height of merged cells if they span only one row, using this (in Module1):

Sub AutofitRowHeight()
    Dim n As Long

    With ActiveSheet.UsedRange
        'reset all used rows
        For n = 1 To .Rows.Count + .Rows(1).Row - 1
            Rows(n).AutoFit
        Next n
    End With
End Sub

but that doesn't work with cells merged over columns (see purple cells D14:F16 in the attached file).

A fuller soulution is in the attached file where there's a single sheet with two buttons at the top:

  1. an orangey one labelled "Autofit merged ranges per row (add border)"  - which will do that plus autofit any non-merged cells with Word Wrap (like the green one) but ignore any without Word Wrap (like the yellow one)
  2. a light blue one to undo that and set rows heights to the default

There are also some cells with text (some of which can't be seen).

If you click on the orangey button, this code runs (with comments to explain what happens):

Sub AutofitMergedColumns()

    Dim m As Long, n As Long, p As Long
    Dim ColWdth As Double, RwHt As Double, MaxHt As Double, MrgWdth As Double
    Dim MrgRng As Range, UsdRng As Range, FreeCol As Long

    ' hide actions
    Application.ScreenUpdating = False
    With ActiveSheet.UsedRange
        ' find first free column to right, allowing for any blank columns
        FreeCol = .Columns.Count + .Columns(1).Column
    End With

    Set UsdRng = ActiveSheet.UsedRange

    ' loop down used range
    With UsdRng
        For m = 1 To .Rows.Count
           'if the row isn't hidden or empty...
            If Not .Parent.Rows(.Cells(m, 1).Row).Hidden _
            Or WorksheetFunction.CountA(.Rows(m)) > 0 Then
              '... reset then work out maximum row height
              MaxHt = 0
              ' loop across row
              For n = 1 To .Columns.Count ' To 1 Step -1
                If Len(.Cells(m, n).Value) > 0 Then
                  'for any merged wrapped cells...
                  If .Cells(m, n).MergeCells = True Then
                    Set MrgRng = .Cells(m, n).MergeArea
                    ' collect column widths
                    With MrgRng
                      MrgWdth = 0
                      If .WrapText Then
                        For p = 1 To .Cells.Count
                            ' accumulate cell widths
                            MrgWdth = MrgWdth + .Columns(p).ColumnWidth
                        Next p
                        MrgWdth = MrgWdth + .Cells.Count * 0.66
                        'write value to row in free column of total width, autofit and get row height
                        With .Parent.Cells(.Row, FreeCol)
                          .Value = MrgRng.Value
                          .ColumnWidth = MrgWdth
                          .WrapText = True
                          .EntireRow.AutoFit
                          RwHt = .RowHeight
                          ' if > max height, replace value
                          MaxHt = Application.Max(RwHt, MaxHt)
                          ' undo write, resetting to Excel default width
                          .Value = vbNullString
                          .WrapText = False
                          .ColumnWidth = 8.43
                        End With
                        ' set height to maximum found and add border
                        .RowHeight = MaxHt
                        .Borders.LineStyle = xlContinuous
                      End If
                    End With

                    ' otherwise autofit any wrapped cells
                    ElseIf .Cells(m, n).WrapText = True Then
                        RwHt = .Cells(m, n).RowHeight
                        .Cells(m, n).EntireRow.AutoFit
                    If .Cells(m, n).RowHeight < RwHt Then .Cells(m, n).RowHeight = RwHt
                  End If
                End If
            Next n
        End If
    Next m
    'remove the free column
    .Parent.Columns(FreeCol).EntireColumn.Delete
    End With

    Application.ScreenUpdating = True
End Sub

It's based on some code I had before and essentially tries the merged texts in a new column, works out the maximum height needed for the row and applies that (for each used row).

It draws a border around the merged cells only so you can see the results - you can find by looking at the comments  then delete or comment out that line in the code.

The reset button runs this simple code:

Sub ResetRowHeight()
    Dim n As Long

    With ActiveSheet.UsedRange
        'reset all used rows
        For n = 1 To .Rows.Count + .Rows(1).Row - 1
            Rows(n).RowHeight = 15
        Next n
        ' clear borders
        .Borders.LineStyle = xlNone
    End With
End Sub

Both should work irrespective of how many merged or wrapped cells you have in the sheet (or sheets if you run the code on a chosen sheet or you loop through them).

Hope this works well for you. If so, please remember to mark this Answer as Selected.

Discuss

Discussion

Why no comment RWSM? Feels like yiu wasted my time snd Willie's time. 
John_Ru (rep: 6212) May 21, '24 at 5:17 pm
Sorry I did not mean to waste your time.  This isn't for font size. Just multiple lines of text that would need to stay in the same row. 
RWSM (rep: 12) May 28, '24 at 2:29 pm
RWSM. Please try the file in my Answer (with macros enabled)-  you'll see it autofits "multiple lines of text" in several (or many) rows and is independent of font size.
John_Ru (rep: 6212) May 28, '24 at 4:09 pm
Yes your right it did. Thank you!!
RWSM (rep: 12) May 31, '24 at 3:33 pm
Glad that helped. Thanks for selecting my Answer, RWSM. 
John_Ru (rep: 6212) May 31, '24 at 5:49 pm
Add to Discussion
0

Hello again RWSM,

There are 3 possibilities of merged cells: case 1) cells in the same row - ex: B3-C3-D3; case 2) cells in the same column - ex: A5-A6-A7; case 3) cells in adjacent rows and columns - ex: C5-D5-C6-D6.

In case 1, if you change the font size the row height will auto fit to the new size (increase or decrease of font size). In cases 2 & 3 auto fit will not adjust row height. Row height must be done manually. It may be possible to do this with VBA but I don't have the time to work out some code.

Update May 18/24

I've had some time to work out some code; here is another option to adjust row height in a merged range.

If the selected cell is not a merged range then nothing is done.

If the selected cell is a merged range then: 1) determine how many rows are in the merged area; 2)  determine the row number of the first row and last rows; 3) calculate the new row height using the selection's formatted font size; 4) if the new row height is calculated to be less than existing, then don't change the height; 5) resize the rows in the selection.

Sub AdjustRowHeight1()

' macro written by WillieD24 for teachexcel.com
' will adjust row height for multiple row merge area

Dim fRow As Long   ' first row of merged range
Dim rCount As Long   ' number of rows in merged range
Dim curHeight As Double   ' current row height
Dim newHeight As Double   ' new row height
Dim rF As Long, rL As Long   ' first and last rows of merged range
Dim fSize As Double   ' font size of merged range

    rCount = Selection.Rows.Count
'    MsgBox rCount
    fRow = Selection.Cells(1, 1).Row
'    MsgBox fRow

' check that selection; exit sub if selection is only a single row
If rCount = 1 Then Exit Sub

rF = fRow
rL = rF + (rCount - 1)
fSize = Selection.Font.Size
newHeight = fSize / rCount
' check new row height vs current row height; if less then exit sub
curHeight = Selection.RowHeight
If newHeight < curHeight Then Exit Sub
' resize row height
With Worksheets("Sheet1").Rows(rF & ":" & rL)
 .RowHeight = newHeight
End With

End Sub

Cheers   :-)

Discuss

Discussion

@Willie - for merged cells on the same row (your B5:C5 example), you don't have to change the font size. You can double click on the divider between row headings (to the left of column A) and the height will autofit. - (The first VBA procedure in my Answer does that kind of thing but doesn't cover all merge cases) .
John_Ru (rep: 6212) May 16, '24 at 1:33 pm
@John
The way I understand it is, that the row height would need to be adjusted to accomodate a new font size. When you change (increase or decrease) the font size the row height automatically changes as needed - no need to double click the divider. (at least with 2007 and 2016)
WillieD24 (rep: 557) May 16, '24 at 1:50 pm
@Willie - true but the question doesn't refer to font size. 
John_Ru (rep: 6212) May 16, '24 at 6:25 pm
@John
What other reason besides font size would there be to need to adjust row height?
WillieD24 (rep: 557) May 16, '24 at 10:48 pm
@Willie - let's not try to guess what the user needs but remember that a merged cell might get fresh content or contain a formula which refers to another cell whose content changes say.

It's starting to look like we might not get a response from the user this time. 
John_Ru (rep: 6212) May 17, '24 at 1:22 am
@John
Yes, those are possibilities, however:
If the merged cells get their value as a result of a formula in the merged cells, the result will be displayed in the font size which the cells are formatted as, thus no row height change required.
If the merged cells get their value/content as the result of a macro, no row height change is required unless the macro changes the font size. If the macro changes the font size, the row height will automatically adjust for the new font size.

Cheers   :-)
WillieD24 (rep: 557) May 17, '24 at 4:06 pm
@Willie - don't forget that a fornula (or a macro or a user) can increase or reduce the amount of text in a merged cell- that might need height adjustment irespective of the font size. Have a good weekend! 
John_Ru (rep: 6212) May 18, '24 at 3:31 am
@John
True, true.
A single cell formatted "wrap text" will automatically adjust row height to accomodate the text.
Merged cells formatted "wrap text" will not automatically adjust row height and will require user (or macro) intervention to adjust row height.
Hopefully RWSM will provide feedback to all of the nfo we have provided.
Enjoy your weekend.
WillieD24 (rep: 557) May 18, '24 at 10:23 am
@Willie - finally got a comment against my Answer but it looks like RSWM didn't read it fully. Guess we'll get nothing more on this so I'll chalk it down to experience. 
John_Ru (rep: 6212) May 28, '24 at 6:14 pm
@John - agreed; and this was another post lacking complete information/details (autofit for multiple rows of data; are the merged cells across a row, in a column, or are they a range of cells)
Cheers   :-)
WillieD24 (rep: 557) May 28, '24 at 11:30 pm
@Willie- RWSM selected my Answer in the end. Sorry about that but at least we now know what was required. 
John_Ru (rep: 6212) Jun 1, '24 at 1:34 am
@John
Good work and glad to see that RWSM didn't leave us hanging (or take/use a solution without giving thanks). What stumped me was the statement "multiple lines of text" in RWSM's post May 28. I took this to mean creating multiple lines by using "Alt + Enter" in the formula bar. I was unable to arrive at a (easy) soluion for this.
WillieD24 (rep: 557) Jun 1, '24 at 1:26 pm
@Willie - thanks and agreed on RWSM not just taking the solution without responding (though I suspect the delay was because he was one of the users not getting email alerts for some reason, as Don found to be the case).

RWSM's "multiple lines of text" might also mean multiple rows but my solution also works for merged wrapped cells containing multiple lines entered using "Alt+Enter".
John_Ru (rep: 6212) Jun 1, '24 at 3:52 pm
Sorry I wasn't more specific. So this is kind of like a purchase order where there is a section where they will need to add line 1 - description of work - amount.
the description of work would be where the merged cells are in the same row. They may need to enter multiple lines of text in that description in Line 1, either by wrap texr ot Alt+enter. 
So if I need this to only be for certain rows and not for the whole document how do I add that?
RWSM (rep: 12) Jun 5, '24 at 12:31 pm
RWSM - we answered your original question so you shouldn't extend it - please ask a new question. Please be sure to describe what you want- not just "autofit rows 12-20" say but also if you need to shrink unused rows below those (e.g. if you want a pricing total and/or footer information in the same place each time). Better still, also include a sample Excel file so we can give a specific reply. Change your company name etc. if you like in that.
John_Ru (rep: 6212) Jun 5, '24 at 4:59 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login