word wrap in Merged Cells- Adjusting Row Height

[ordoff73]

I currently have a group of cells merged together where I am using the Concatenate formula. The worksheet is not edited by the user only printed. The problem is this cell has the capability of being 1 line or eight lines long. Since the cells are merged the autofit format and the word wrap do not automatically adjust the row height.

I need a formula or macro that is completely self contained, where the user does nothing to activate, where it will appear to be automatic from the users view point. This sheet will be protected where nothing can be edited by the user only printed. I am fairly proficient when it comes to formulas but macros I am a beginner.


any help that you can provide will be greatly appreciated

[Danny William]

Hi

This should do it with limitiations, the users would have to leave the cell and then return for it to take effect

Please Login or Register  to view this content.

I found this quite a few years ago but the basic advice was do not work with merged cells.

Danny

[ordoff73]

unfortunately I had to use a merge cell due to the make-up of the other cells in this worksheet. Thank you so much for your quick reply, however I have two problems that I did not anticipate in attempting this.

It work exactly as I requested only it added too much height to the cell, To clarify by how much; each line is 15 points in height. My merged cell has the possibility to be between 1 and 7 lines.
If the Merged Cell is:

1 Line, Macro Result = 15 point height (correct)
2 Lines, Macro Result = 45 point height (incorrect should be 30)
3 Lines, Macro Result = 75 point height (incorrect should be 45)
4 Lines, Macro Result = 75 point height (incorrect should be 60)
5 Lines, Macro Result = 105 point height (incorrect should be 75)
6 Lines, Macro Result = 135 point height (incorrect should be 90)
7 Lines, Macro Result = 150 point height (incorrect should be 105)

This adds to much space to the sheet

There has to be exactly 1 inch between the bottom of this cell and the top of the next cell, and the top of the cell must be flush with the cell above it. With the results as variable as they are I cannot get it to work.

Furthermore, when the cell changes from large to small it does not reduce in size. I am too much of a novice to even know where to begin in editing this macro to get it to accomidate these needs.

Thank you again for your help

[mudraker]

Danny William

Please take a couple of minutes and read the Forum Rules Your reply broke rule 3
I have wrapped your VBA code for you

I am also closing this thread as ordoff73 has started a new threa din the Programming forum