Resetting row height after removing text with AutoFit Row Height

[JPhotonics]

It appears that I cannot set rows to AutoFit if I have cells merged on that row which would need to be the ones being AutoFit.

I have found a workaround by creating a hidden column off to the side which will display the same text found in the cells that need to be AutoFit. That duplicate cell is then set to AutoFit the row and that expands the row properly to fit my comment from the original merged cells.

The problem is that if the Comment is then deleted, the row height does not reset to where it was before the AutoFit expansion was made. I assume I will need a macro to accomplish this task. I have already used conditional formatting to reset the color if the comment is deleted, but I'd also like to remove the row expansion.

I have attached my worksheet as well as a screenshot below:

 

[bobhc]

The problem with merged cells is that when you want to do something to an individual cell Excel does not know which of the merged cells you are working with and because the cells are merged it wants to apply all to all, wont work.
The merged cells option should be removed by Microsoft.

Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.
For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.

In addition, not all cell formats, stick once you emerge a cell.

You can't sort a column with merged cells.

You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.

You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!

Merging cells in columns and rows could lead to data loss, bad thing.

Formulas and Functions that refer to merged cells will not work, bad thing.

Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.

Centre Across Selection is a far better alternative to merging.

To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Centre Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.

 

[JPhotonics]

Thank you for your input, but I don't think that will fix my issue.

I do not want my text centered. I want it left justified, but that is not a problem here. I tried to un-merge them and the AutoFit row height still doesn't work, it just keeps it at the AutoFit height even if I delete the contents and the added row height is no longer needed.

 

[JPhotonics]

Range("B12:E12").Rows.AutoFit
Range("B17:E17").Rows.AutoFit
Range("B22:E22").Rows.AutoFit

I think I have figured it out. I kept the cells merged, but added VBA code to AutoFit the rows (shown above). I did have to copy/display the results of the cell into another cell to make it work, but it appears to work properly. I'll just need to hide this column when I am done. The key is to only delete the information contained in the "Comments" cell, while leaving the formula alone over in the H column (which I will hide).

I have included a couple of screenshots to show it working.

Notice the comments w/ multiple rows, rows have expanded automatically:


Next I deleted the cell w/ comments and it automatically reset the Row height:

 

[bobhc]

You can still justify text after you have used Centre Across Selection. But if you want left justification why bother with merge?

 

[JPhotonics]

You can still justify text after you have used Centre Across Selection. But if you want left justification why bother with merge?

Because I have multiple cells of varying width in my sheet. The conditional formatting works properly with them merged also.