Autofitting row height with merged cells

[Fyresparxx]

I've been doing some research trying to figure out how to solve this problem, but I'm not making much headway.

I have a data analysis report. We collect data on various pieces of machinery, and our program prints the numbers into its own simple report. I paste this report onto sheet two of my workbook and I have a VLOOKUP that pulls the numbers off 2 and inserts them into the correct places on the report (sheet 1). We then go down the list and analyze the data and insert our analysis and recommended actions under each piece of equipment. This report gets printed and sent out with a summary page at the beginning. The summary page(s) pull the information off of the bottom of each page of equipment data. This is all working as intended. However, I need to use merged cells to get the information to print correctly, and all data must be entered into a single page on the data cell to show up in the summary. Hard returns are used to multiple lines in the analysis field, usually no more than 4 lines. I need to auto fit the height on all merged cells on the sheet to show all the analysis data.

Center across selection will not work because it makes the report look unprofessional and messy. I've seen multiple posts about a macro by a Jim Rech that works, but they all lead to dead links. I've tried a few different macros, and either they don't affect all cells in the sheet, they don't adjust the height properly, or they crash excel. I'm not sure what I'm doing wrong.

Is it because I have so much data in the sheet? Or is there a different macro I should be using?

Using Excel 2010 on Windows 8.

 

[Jon von der Heyden]

Merged cells are considered a big no-no in this community, and this is one of many reason to avoid them.

Have you considered embedding a text box, or if you want nicer formatting then even better yet a Word object? You can set properties to move and size with cells (or not, if you prefer), and generally speaking this is the sort of object that exists for exactly the purpose you describe.

 

[Fyresparxx]

I would avoid them if I could figure out how to make the report work and display properly without them.

I need (as an example):
c89 - =c388
c90 - =c389
c91 - =c390
c388 - =IF(C459="","",C459)
c389 - =IF(C465="x","**Everything Appears to Be in Normal Operating Order",IF(C461="","",C461))
c390 - =IF(C465="x","",IF(C463="","",C463))
c459 - Notes
c461 - Assessment
c463 - Recommendations

*All entries are merged cells across c:j at the moment because I need wrap text to display hard returns correctly, and I need field to be the entire width of the page.

Sometimes I have three lines of Assessment, or two lines of recommendations and so forth. But I need c89=c388=c459, where all three update based on whatever is entered into 459. To further complicate things I have a couple of pieces of conditional formatting applied to the entire sheet. Any time the block says "no data collected" it needs to be purple, and any time "Everything Appears to Be in Normal Operating Order" it needs to be green. Can I do all of this with text boxes? or word objects for that matter?

I'll try whatever will work.

 

[Fyresparxx]

Still looking for an answer to this. I feel like a macro is the solution, because at one point I had one working (partially). Even If I had to manually change the height of some rows, as long as I could reliably tell what rows I needed to look at it would be faster than the current method we've been using to generate the report (endless copy and pasting and manual lookups to data in Word).

Is there a way to set up a conditional formatting rule to highlight cells with hidden data? Or even just highlight cells with multiple lines of text I suppose. It's not the preferred solution, but I need SOME solution. Manually adjusting rows is preferable to hiding important data in a cell too small to hold it.