Excel 2007 : Auto-adjust row height on VLOOKUP?

**cornishdave** · 08-15-2010, 08:16 AM

I have a sheet of data including a column into which I am entering text. The cells in this column are formatted as 'general' with text autowrap and the height increases with additional lines of text exactly as it should.

My problem is that I have another sheet in the same workbook which uses VLOOKUP to pull information through from two other sheets including the one mentioned above and display the information as a form but the cell containing the text, although formatted the same, does not grow with extra lines of text but remains at its original height.

Is it possible to have the 'form' cell grow as necessary with extra text?

**teylyn** · 08-15-2010, 09:06 PM

Hello cornishdave,

if the cell with the Vlookup formula is a merged cell, then I'm afraid you're out of luck. Merged cells do not auto-adjust the row height at all. You'll need to manually adjust it.

If it's not a merged cell, then you can run a macro when the sheet is calculated to recalculate the row heights. A formula does not trigger a recalculation of row height when the result of the formula changes. But if the sheet is calculated, the row height can be checked.

Put this code into the sheet module and enable macros.

Please Login or Register  to view this content.

**cornishdave** · 08-16-2010, 07:59 AM

[I]

Originally Posted by teylyn

Hello cornishdave,

Put this code into the sheet module and enable macros.

Please Login or Register to view this content.

Hi Teylyn and thanks for your reply.

Unfortunately, Visual Basic is a bit outside my experience so I'm struggling here. I don't have any merged cells on any of the sheets in the workbook. I have opened VB Module and pasted the code. I've also been into Macro Security and enabled Macros and saved the file as macro-enabled but it still doesn't work.

I've attached a copy of the file (with images removed to reduce file size) in the hope that you might see what I'm missing.

Thanks, cornishdave

**teylyn** · 08-16-2010, 08:48 AM

Hello cornishdave,

Unfortunately, Visual Basic is a bit outside my experience so I'm struggling here.

No worries. None of us were born with VBA knowledge. You were actually very close to a working solution.

You put the code I posted into a standard module. But the code I posted monitors a sheet for an event (the sheet being recalculated, because cell contents have changed). So this code is triggered by a Sheet event (if the sheet calculates, the code runs), but for this to work, you need to put it in the sheet module.

Right-click the Invoice sheet tab, click View Code and paste the code into the code window. Close the VBE and then test.

See attached for a working solution.

cheers

**cornishdave** · 08-16-2010, 09:40 AM

Fantastic! Sorted - thanks again, Teylyn.

**EXceljb** · 07-03-2012, 09:29 AM

Hi,

The code pasted above is working for the whole sheet even for the hidden rows and the hidden rows are getting unhidden.Could there be any other way to not display the hidden rows and apply the Auto-adjust row height on VLOOKUP for only selected rows? Please reply...

**jeffreybrown** · 07-03-2012, 09:34 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.