» Adjust row height of merged cells using VBA in Microsoft Excel

CATEGORY - Formating in VBA VERSION - All Microsoft Excel Versions

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

Book Store:

VIEW ALL BOOKS

Recommended Books:

Email Tip

Print Tip

Add to My Tips

Rate This Tip



1	2	3	4	5

Rating: 3.76 Views: 38531

Sum of width doesn't add up to merged width.

Bart Deschoolmeester Posted on: 31-12-1969

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?

Reply: Sum of width doesn't add up to merged width - Bart Deschoolmeester from Belgium wrote on August 14, 2003 8:19 AM EST

Alan Posted on: 31-12-1969

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

Reply: Above

Alan Posted on: 31-12-1969

Please ignore my first section of the reply - I was on a different planet!

Sorry!

Alan.

Reply: Above

Bart Deschoolmeester Posted on: 31-12-1969

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?

Reply: Cell Widths - Bart Deschoolmeester from Belgium wrote on August 18, 2003 3:55 AM EST

Alan Posted on: 31-12-1969

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.

Reply: Above

Bart Deschoolmeester Posted on: 31-12-1969

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