Auto-expanding merged Excel cells

Last year I started using Excel for my marking. I created rubrics of all the things I was looking for, and set up formulae so that all I need do is to put a character - I use "X" - into a cell to have Excel load a value and add up the totals. I still leave a textbox at the bottom of the page to provide additional feedback, but using - and continually editing - a really, really thorough rubric has simplified and sped up marking.

It is also incredibly useful for internal parity in marking, as I have a team of supervisors on the same course. The rubrics really help us all look for the same things and provide consistency for our supervisees.

However, there are some things that are troublesome in Excel. One being getting the additional feedback box to auto-expand via AutoFit. Apparently AutoFit is problematic because the function won't work on a row that contains a cell merged with cells in other rows (same deal with columns, but my problem is rows not auto-expanding).

Because I protect the marking sheets to avoid other markers making inadvertent mistakes in the underlying formulas, at the moment we either have to trim our text to fit; or to remove the protection, auto resize the merged cell, then put the protection back on again. A small thing, but annoying.

Luckily, Superuser (18 June 2012) set up a piece of code which works in the background of a macro-enabled (.xlsm) workbook to adjust the height of the row to fit all the text in a merged cell. Superuser's main code can be found here.

However, Superuser's code does not detail turning off protection and then turning it back on again. You still need to add the following, in the following places:

At the beginning, after "Dim str01 As String", add a new line, then:

With Me

wasprotected = .ProtectContents

If wasprotected Then .Unprotect

At the end, after "End If", add a new line, then:

Range(str01).MergeArea.Locked = False

If wasprotected Then .Protect

End With

Experiment on copies of your workbook, because we don't want to get into a circular argument that won't undo! However, this worked for me, and hopefully it will work for you as well.



Sam

• Reference: Superuser (18 June 2012). Autofit height of a merged cell in Excel 2010. Retrieved from https://superuser.com/questions/438445/autofit-height-of-a-merged-cell-in-excel-2010