AutoFit Height ONLY not width

Hi,

As you have noted merged cells cause problems. In fact many of us consider they are the devil incarnate and just not worth bothering with since they just cause too many problems.

Can I ask why you need all these R:DE merged cells. What does doing it this way give you that a more normal layout with one piece of data per cell doesn't?

Hi,

Thanks but that doesn't answer the question. It just seems you are making life difficult for yourself for no discernible reason, hence my question. Why is this layout so vital when a single cell in a wider column would achieve exactly the same without the hassle. Why can't all the sheets be changed in a similar way. Why have bad design on other sheets dictate bad design on this sheet.

I'm not overlooking the AutoFitRowHeight functionality.
As I've stated, this is not a page that the user will see until they have printed the document. Any text in these 4 rows comes from a different worksheet, and can vary in length depending how other sections of the document have been filled in. This means that the user will never get the option to select AutoFitRowHeight as they will not have access to this sheet within excel.

I've tried on blank worksheets having sheet1 A1 set as "=Sheet2!A1" then setting a column width and clicking AutoFitRowHeight. If I then enter something longer than the cell width of sheet1 A1 into sheet2 A1 the height of sheet1 A1 does not change. It will only change if I select AutoFit after the text has been entered into sheet2 A1.
I've also tested clicking AutoFit where cells A1:J1 are merged and still have "=Sheet2!A1" as the value, but also have M1 as =Sheet1!A1. If I then click AutoFit on M1 the height will change and this will also affect A1:J1 - this is the effect i'm looking for but without having to manually click AutoFit.

You have no idea how hard I've tried to get this document remade (for the last 4 years at least) and I was only given the go-ahead 6 months ago, however I've never been included in any discussions about any changes (even though I'm prectically the only person in the office with knowledge of the entire document) but I keep receiving notifications with comments like "as agreed in the meeting". Management think that any changes will take a week maybe a month, however I've been working my way though this for the past 6 months and there's at least another 6 months worth of work to do.

Basically there's two parts to the document; 1. What the user sees, 2. What the client sees. (the above is from #2). The file that's actually sent back and forth between the users and the office is an XML file which assigns all the values in the excel file. Part 2 of the document automatically completes based on the information in Part 1 and is converted into a PDF document for the client. I've spoken with the person who created the original document 15 years ago, and they say there's no reason that excel needs to be used - we only use excel as the software to enter the values for the XML file. They say we can just develop something in C# that would do the same task and be much quicker for the user. The problem is, I don't work in the IT department and have a very limited knowledge of C#. All of the development resources are currently working on other 'more important' projects, which is why I've been given this task and told it needs to stay in excel (to save on development).

Originally Posted by DHFE

I'm not overlooking the AutoFitRowHeight functionality.

.... This means that the user will never get the option to select AutoFitRowHeight as they will not have access to this sheet within excel.

I've tried on blank worksheets having sheet1 A1 set as "=Sheet2!A1" then setting a column width and clicking AutoFitRowHeight. If I then enter something longer than the cell width of sheet1 A1 into sheet2 A1 the height of sheet1 A1 does not change. It will only change if I select AutoFit after the text has been entered into sheet2 A1. (my emphasis)


...this is the effect i'm looking for but without having to manually click AutoFit.

I think AutoFit Row height is probably similar to the Volatile functionality associated with certain functions. Whenever I need to do something like this which requires and action to take place I generally attach a simple one line macro to some relevant event. In your case that event could be a SheetChange event when column Q, Or whatever) is changed. i.e. the macro would be

IF Target = Range("Q" & Target.Row) Then Target.Rows.Autofit

If it were up to me I wouldn't be making the final document in excel, but that's what I've been told must be used.

How would you change the layout though? The 4 lines I'm looking to change the hight are all different widths and need to take up the space already dictated by the current design. This is why i've added the same content in single cells (DP-DR). The cells all line up with the relevant rows so any height changes to them will also affect the main section.

I should probably also point out that the uploaded document only contains part of what is on the same worksheet in the final document. There's 100s of questions going down the page and ones further on require the layout to be different again. As this isn't a page that the user will edit directly the layout I have allows for the company branding to be added coherently throughout as well as with other pages.

Hi,

You appear to be overlooking the AutoFitRowHeight functionality. This won't however work with merged cells.

The approach I'd use is to make sure all the data for the 4 rows were in the same column. Say column R and then just align left or right as appropriate. For instance.

The F21 formula could be in column R
F22 in column R would be ="Question "&DG22 &" "&CX22
F23 & F24 could be in a column Q since presumably the words Observations and Comments don't need any wrapping since they'll always occupy the same width

Sometimes management who may have no idea about these things have to have gently explained to them the advantages and limitations of various software and advised that sticking rigidly to some pre-conceived formatting idea just isn't efficient for the software (i.e. Excel) that they want to use.

It seems to me quite pernickety to be worrying about the formatting border lines around groups of cells being in Precisley the same position as say a Word document. Presumably management are wanting to use Excel for some reason and they should be aware that you can;t always fit square pegs into round holes, leastwise not in any efficient or cost effective way.

Sorry, i've never used Target before. Could you please explain that code and how it should work?

You do understand that this is VBA macro code and not an Excel function?
I'm happy to explain it if you really do want to go down the road of yet another change to the workbook. I'm conscious though that your management seem reluctant to change.

Yes please, I've been using VBA in other sections too - but I've been teaching myself and only used it for the past 6 months on/off. Basically what I'm doing to this page is merging two-three different sections. Originally this section would only contain the Action and Action Taken parts. The Question and Observations were a different section. Comments didn't appear originally.
We've been asked by most clients to produce a smaller document. The Question & Observation section would show every question and every observation (roughly 40 A4 pages), the action and action taken section would only contain ones where there was a issue raised (usually 3-4 A4 pages) and it was a bit disjointed to reference back to the question and observation section; we had to field quite a few phone calls over the years to try and explain things (even though all the instructions are in the document). So what I'm trying to to is remove the question & observation separate section and merge it with the action section but still so the client only sees ones where there's an issue.


EDIT:

Actually i think have figured something out, not sure exactly how I got it to work, but it does; and from messing around with it I've realised I need to set up the code on the sheet where inputs are being made, as it doesn't react to changes on the sheet where the values are formulas. Only on pages where the user has made a change.

So i've inserted this code to sheet 2

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets("Sheet1").Range("B21:DE24").EntireRow.AutoFit
    Worksheets("Sheet1").Range("DP31:DR34").EntireRow.AutoFit
    Worksheets("Sheet1").Range("DP41:DR44").EntireRow.AutoFit
    Worksheets("Sheet1").Range("DP51:DR54").EntireRow.AutoFit
    Worksheets("Sheet1").Range("DP61:DR64").EntireRow.AutoFit
    Worksheets("Sheet1").Range("DP71:DR74").EntireRow.AutoFit
    Worksheets("Sheet1").Range("DP81:DR84").EntireRow.AutoFit
End Sub

As you can see i've also checked it against the merged cells B-DE and it still works. So there isn't really the need for the DP-DR columns.

Basically all those ranges are the rows i want to auto adjust the height when the question wording changes. They're all linked to a cell in Sheet 2. If you make a change to one of those cells on sheet 2, the height of the cell on sheet 1 will automatically adjust to the correct height to fit the text.

As i said i'm a bit of a self taught novice on VBA so would be grateful if you could double check this for me?
So for example; (with the above code in Sheet2)
row 21 (F21:DE21) should have the value something like "=Sheet2!B1". Then if you make a change to sheet 2, if B1 contains a value longer than that of sheet 1 F12:DE21 it should automatically change the height of row 21 to fit because wrap text it active.

EDIT

Just FYI, the helper columns do appear to be required - no matter which ones you reference in the code.