Autofitting row height with merged cells

Fyresparxx

New Member
Joined
Mar 21, 2014
Messages
18
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 21, 2014
Messages
18
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.
 
Last edited:

Fyresparxx

New Member
Joined
Mar 21, 2014
Messages
18
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.
 

Forum statistics

Threads
1,136,777
Messages
5,677,673
Members
419,711
Latest member
dacrmcvega0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top