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
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.
This works great to expand the row but how can I get it to go smaller if there is less text than my previous record?
I've created an invoice sheet as a work around for our dispatching software. Long story short, one of my fields is the summary of the job. Each summary varies in size. I'm running a vlookup to filter through each record to print. When a short summary comes up I would like it to shrink so there is not a lot of extra space between the summary and pricing. I've never posted a question before so if this is the wrong way to go about this, I apologize in advance.
How can I adapt this to apply only to a specific range of cells?
"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 "
"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."
"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? "
"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"
"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"
"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?"
"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
"
"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."
"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? "
Please ignore my first section of the reply - I was on a different planet! Sorry! Alan.
"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"
"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?"