Merged cells / Row height (2000)

[tmg9671]

I have text in merged cells with word wrap, but the row height will not automatically adjust when more text is added.

Is there a way to have the row height automatically adjust for merged cells?

Thank you.

-tmg9671 <img src=/S/please.gif border=0 alt=please width=31 height=23>

[slrosen]

I'm pretty sure there isn't. I've just tried various combinations of formatting and height features, and it seems that merged cells can't do automatic height changes for word wrap. You can manually adjust the height of a row with merged cells (you've maybe tried that), but I don't think you can get row height to automatically adjust for merged cells.

[sdckapr]

Check out <post#=270923>post 270923</post#> for some code as a workaround.

Steve

[tmg9671]

Thank you for the instructions, but I still cannot get it to work, manually or automatically. I followed all the directions but it still will not work. Does it matter which module the code is located in? Please advise. Thank you.

-tmg9671

[sdckapr]

The AutofitMergedCellRowHeight from the website link, goes in a standard module.

The "Worksheet_Change" code that I provided in the post, does not go into a module. It goes into the worksheet object of the sheet with the merged cells. It works on the cell A1 you will have to adjust the range as appropriate to run whenever those cells change.

Steve

[tmg9671]

I'm sorry. It still doesn't work.

Does it matter which module the AutoFitMergedCellRowHeight code is in? I have four of them in the VBA.

-tmg9671

(I am really starting to feel like a doofus!!)

[sdckapr]

It can go into any module.

Some questions:
1) do you have "wrap text" selected in the cell(s) of interest? [It won't expand if you don't wrap, just like the autofit]
2) if you have cell that is merged (and is marked to "wrap text") and you select it and run the macro, does it work? [then the macro works fine]
3) If 1 & 2 are okay the problem is with the worksheet change macro.
What cell(s) are you trying to autowrap?
What sheet are they in?
Where do you have the "change" macro?
What modifications did you make to it (as I mentioned earlier, I posted only an example that was set assuming ONLY cell A1 was to be adjusted, it needs to be adjusted to change whatever cells you are interested in)

Steve

[tmg9671]

Okay...

1. "wrap text" is selected
2. yes. the macro works fine if I manually select the cell and manually run the macro.
3. >the cell that changes is B25. I changed the code to reflect that. (Note: B25 is B25:V25 merged)
>they are in a sheet called "Contract"
>the "change" macro is in sheet 1 (Contract)
>the only modifications are to cell B25

-tmg9671

I think we are almost there...

[sdckapr]

Did you change the macro to look for changes in B25 as below?

<pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B25")) Is Nothing Then
Target.Select
AutoFitMergedCellRowHeight
End If
End Sub</pre>


Steve

[tmg9671]

I found the problem...

The data in cell B25 is actually read from a refreshed query table. ie, I am using the command "=Query!D2" where Query is the sheet with the refreshed external data and D2 is the data I need to go into cell B25 on the Contract sheet.

The macro works automatically if I enter data manually into the cell B25, but when it reads from Query!D2, it doesn't work.

I tried putting the query in the contract sheet so that the data appears beginning in cell AC25 (same row but off the printed page) in order to get the refreshed data to force the line height automatically, but now I have the problem where the column width is fixed whenever the data is refreshed.

I know that I am rambling on, but this is the best way that I can describe it. You have helped me tremendously, and I believe that I am 98% there...if I can just get past the above glitches.

As always, thank you for you help.

-tmg9671

[sdckapr]

The macro is triggered by the change in B25. If you don't change the contents of B25 the macro is not changed. Having a formula in B25 means that the contents will not change (the "value" changes), but not the contents of the cell ("=Query!D2"). This stays the same, not triggering a change.

try something like this in the query sheet:
<pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("d2")) Is Nothing Then
worksheets("contract").activate
Range("b25").Select
AutoFitMergedCellRowHeight
End If
End Sub</pre>

This should run whenever Query!D2 changes and then will change the row ht of contracts!B25

or even just add the lines of code:
<pre> worksheets("contract").activate
Range("b25").Select
AutoFitMergedCellRowHeight</pre>


to a macro you might have that refreshes the data.
If you don't use one, create a macro that refreshes the data then updates the cell contract!B25.


Steve

[tmg9671]

Halleluja!!!!!!!

Steve, thank you soooooooooo much for all of your help. I couldn't have done it without you.

BRAVO TO STEVE!!!!!!!

-tmg9671

<img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

[tmg9671]

Okay, I hit a glitch in this process...

The process works perfectly if the data in the row is 32 lines or less. (The row height will only increase to 409.50)

I tried merging two rows in order to increase the space, (B25:V26) but now the height of the two rows do not adjust (increase or decrease).

How can the coding be adjusted to accomodate more that one merged row?

-tmg9671

[sdckapr]

RowHeight is limited to 409. This is an excel problem, not the macro problem.

To do 2 (or more rows that are merged) change 3 lines:
This line makes sure it only runs if you have 1 row merged:
<pre>If .Rows.Count = 1 And .WrapText = True Then</pre>

Change it to:
<pre>If .WrapText = True Then</pre>


This line sets the "CurrentRowHeight" variable to the mergeArea .RowHeight. This will give an error if the rows are merged (the "rowHeight = "Null") we will by pass it by just checking the activecell row height:
From:
<pre>CurrentRowHeight = .RowHeight</pre>

To:
<pre>CurrentRowHeight = ActiveCell.RowHeight</pre>

And lastly for the same reason as above, Change from:
<pre>PossNewRowHeight = .RowHeight</pre>

To:
<pre>PossNewRowHeight = ActiveCell.RowHeight</pre>


You might have to change the "0.71" factor used for adjusting. I am not sure where it came from. I would guess it was trial and error. It might be necessary to adjust it based on the zoom which can affect the looks sometimes.

I will warn you, before you rack your brain and then post again, the next problem you will encounter, since you seem to working with very large strings in the merged cell is that if you have over "approx 1024" characters to display (the exact number depend on many factors), it will not work. It will only set the row necessary to display the 1,024 characters, no more. Manually expanding will not display more. This is a "bug"/ "feature" in excel that you will have to live with. This is not a merged cell issue. You would have the same problem even without using merged cells.

The only options are to use a textbox or to separate the whole string into substrings (by paragraphs?) into separate cells, then you could run the macro on each of the cells to adjust each one's row height



Steve