Adjust row height of merged cells using VBA in Microsoft Excel

Question:
Merged cells do not have the correct height after a row break. How can I correct this?

Answer:
Insert the following code in the standard module.

Sub AutoFitMergedCellRowHeight()
    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    Dim CurrCell As Range
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    If ActiveCell.MergeCells Then
      With ActiveCell.MergeArea
            If .Rows.Count = 1 And .WrapText = True Then
                Application.ScreenUpdating = False
                CurrentRowHeight = .RowHeight
                ActiveCellWidth = ActiveCell.ColumnWidth
                For Each CurrCell In Selection
                    MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
                Next
                .MergeCells = False
                .Cells(1).ColumnWidth = MergedCellRgWidth
                .EntireRow.AutoFit
                PossNewRowHeight = .RowHeight
                .Cells(1).ColumnWidth = ActiveCellWidth
                .MergeCells = True
                .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
                  CurrentRowHeight, PossNewRowHeight)
            End If
        End With
    End If
Application.ScreenUpdating = True
End Sub

Users are saying about us...

  1. “AutoFitMergedCellRowHeight works fine, but at the end of the For Each loop MergedCellRgWidth doesn’t add up to width up to the width of the merged cells.
    For example: Merge 4 rows (each of a width of 20): the width of the merged cell is a bit more than 80 (around 83). Sometimes this could give you a blank line (the text now fits one less lines).
    Does anybody know where the extra width comes from? And how to calculate it?”

  2. “Hi Bart,
    Just to check, are you clear about the difference between rows and columns? Could that be causing the issue.
    No offence meant, but you say above that you merged four rows of width 20, and got one greater than 80.
    However, if you merge four rows (width 20), the combined cell should still be width 80, although the height will obviously have increased.
    If you are talking about merging four columns, you need to take into account perhaps the border width between the cells.
    Four individual cells of width 20 will contain about 83 width if you merge them from the two inside edges of the single large cell.
    On reflection, I think that must be what you are asking?
    Apologies if I missed the point!
    Alan”

  3. “Hi Alan,
    don’t apologies: you were right: replace ‘Merge 4 rows ….’ with ‘Merge 4 cells …’.
    I don’t know if you are right about the borderwidth though.
    1. Changing the border of any cell does shifts the cells down a bit (so the height of a merged cell could be dependent of the borders of merged cells – but this is irrelevant to the AutoFitMergedCellRowHeight procedure); but in my Excel version (97) changing the borders doens’t shift the cells right, so I think it doesn’t matter to the width of the merged cell what type of border its merged cells have.
    2. But even without any borders the width of the merged cell doesn’t add up to the sum of the widths of its merged cells. But the only way (at this moment) for me to know what the real width of the merged cell is, is to drag the first column of the merged cells until it equals (just place it right above) the right border of the merged cell.
    At this point i haven’t found a way to predict (calculate) the width of the merged cell.
    At first i thought if i would merge four cells three ‘inside’ borders would disappaer. But it’s not like that a border (well acctually it’s gridlines, because my merged cells don’t have borders) has a fixed width.
    Merging 4 cells of width 10 gives me 42
    Merging 4 cells of width 20 gives me 82.3
    So to me there’s no logic.
    Any ideas? “

  4. “Interesting – I have never really played with this much.Try changing the borders to a wide (thick) line – it seems to make some difference, but I cannot see a logical pattern (as you note).
    Alan.”

  5. “As I said: to me the width of a border doesn’t have any influence on the width of the merged cell. Trying to guess the width of the merged cell could backfire as when you’re over the actual width some lines of text would not be visible to the user. I think that it’s best to leave the code as it is. And live with the fact that sometimes a blank line appears in the cell before the start of the text.
    Thanks

  6. “AutoFitMergedCellRowHeight works fine, but at the end of the For Each loop MergedCellRgWidth doesn’t add up to width up to the width of the merged cells.
    For example: Merge 4 rows (each of a width of 20): the width of the merged cell is a bit more than 80 (around 83). Sometimes this could give you a blank line (the text now fits one less lines).
    Does anybody know where the extra width comes from? And how to calculate it?”

  7. “Hi Bart,

    Just to check, are you clear about the difference between rows and columns? Could that be causing the issue.

    No offence meant, but you say above that you merged four rows of width 20, and got one greater than 80.

    However, if you merge four rows (width 20), the combined cell should still be width 80, although the height will obviously have increased.

    If you are talking about merging four columns, you need to take into account perhaps the border width between the cells.

    Four individual cells of width 20 will contain about 83 width if you merge them from the two inside edges of the single large cell.

    On reflection, I think that must be what you are asking?

    Apologies if I missed the point!

    Alan”

  8. “Same result as dr mom – cd type “”wrap text”” into the style box, but it disappears.
    Could not find this topic in Excel Help, either”

  9. “Hi Alan,
    don’t apologies: you were right: replace ‘Merge 4 rows ….’ with ‘Merge 4 cells …’.
    I don’t know if you are right about the borderwidth though.
    1. Changing the border of any cell does shifts the cells down a bit (so the height of a merged cell could be dependent of the borders of merged cells – but this is irrelevant to the AutoFitMergedCellRowHeight procedure); but in my Excel version (97) changing the borders doens’t shift the cells right, so I think it doesn’t matter to the width of the merged cell what type of border its merged cells have.
    2. But even without any borders the width of the merged cell doesn’t add up to the sum of the widths of its merged cells. But the only way (at this moment) for me to know what the real width of the merged cell is, is to drag the first column of the merged cells until it equals (just place it right above) the right border of the merged cell.
    At this point i haven’t found a way to predict (calculate) the width of the merged cell.
    At first i thought if i would merge four cells three ‘inside’ borders would disappaer. But it’s not like that a border (well acctually it’s gridlines, because my merged cells don’t have borders) has a fixed width.
    Merging 4 cells of width 10 gives me 42
    Merging 4 cells of width 20 gives me 82.3
    So to me there’s no logic.
    Any ideas? “

  10. “Interesting – I have never really played with this much.

    Try changing the borders to a wide (thick) line – it seems to make some difference, but I cannot see a logical pattern (as you note).

    Alan.”

  11. “As I said: to me the width of a border doesn’t have any influence on the width of the merged cell.

    Trying to guess the width of the merged cell could backfire as when you’re over the actual width some lines of text would not be visible to the user.
    I think that it’s best to leave the code as it is. And live with the fact that sometimes a blank line appears in the cell before the start of the text.
    Thanks “

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube