AutoFit Row Height not working

[Rob_010101]

Hello,

my UNIQUE, FILTER formula is pulling through results but autofit row height is refusing to work.

I have selected all cells and used autofit row height and applied wrap text but it is not working. I have to re-select and apply these every time the formula pulls through a new result. When new information is entered, the UNIQUE, FILTER returns the result with the row collapsed.

The only way I think I can get around this is with a piece of VBA code which selects cells A8:D10, A12:D14 and A16:D45 and applies wrap text and autofit row height to these cells, when the sheet (Objectives annual review) is opened. Here is my sheet:

Annual Review Template DRAFT.xlsx
	A	B	C	D
1	Annual Objectives Review
2
3	Line manager name & job title		Date of annual review
4	Second reviewer name & job title		Date of second review
5	Objectives	Line manager review	Employee self-review	Status
6
7	Company-wide objectives
8	Employee engagement
9	Test test test test test test test test test test test
10
11	Employee's personal objectives
12
13
14
15	Role profile objectives
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Objectives Annual Review

Cell Formulas
Range	Formula
A8:A9,A12	A8	=IFERROR(UNIQUE(FILTER('Setting Annual Objectives'!A9:A11, 'Setting Annual Objectives'!A9:A11<>"")), "")
A16	A16	=IFERROR(UNIQUE(FILTER('Setting Annual Objectives'!A17:A46, 'Setting Annual Objectives'!A17:A46<>"")), "")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3	Cell Value	contains "Choose from list..."	text	NO
D5:D1048576	Cell Value	contains "Exceeds expectations"	text	NO
D5:D1048576	Cell Value	contains "Meets expectations"	text	NO
D5:D1048576	Cell Value	contains "Needs development"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D8:D10	List	Needs development, Meets expectations, Exceeds expectations
D12:D14	List	Needs development, Meets expectations, Exceeds expectations
D16:D45	List	Needs development, Meets expectations, Exceeds expectations
D3:D4	Date	between 01/12/2024 and 31/12/2099

 

[jkpieterse]

Autofit doesn't work dynamically. That is, if a cell contains a formula that results in text and after a calculation the formula results in a different amount of text, no autofit is triggered. It only works during entering of text, for the cell(s) you are entering the text into. Provided word wrap is turned on and you haven't merged the cells in question.

 

[Rob_010101]

Thanks, I pretty much figured that.

Could this be automated through VBA to run when the sheet is opened?

 

[jkpieterse]

If the sheet is called Sheet1, add these lines to your workbook_Open event in the ThisWorkbook module:

ThisWorkbook.Worksheets("Sheet1").Range("A1:D100").Rows.Autofit

You must adjust the cell address to fit your needs (you can replace it with a named range too). If you want to have this work for all used cells, the code becomes:

ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Autofit