Resizing cells to fit text returned by VLOOKUP

[Michelle]

I have some VLOOKUPs which sometimes return 50 or 60 characters (they're in
merged cells). I want the text to wrap, and the cell to enlarge to fit the
text - Just like it does whan you type it in, but somehow, because it is
being returned from a formula it just displays the bottom line of the
wrapped text, and doesn't resize the row.

Anyone know how I cam make it fit?

I'm happy to use a VBA solution if there's no built in way to make it happen
automatically.

Thanks

M

[Joerg Mochikun]

Would you mind to limit your request to ONE newsgroup?
Joerg

"Michelle" <mh_lond...@hotmail.com> wrote in message
news:44EB431F-4EDB-4AD1...@microsoft.com...

[Michelle]

Yes, I do mind. I get lots of stroppy replies if I post similar requests to
more than one group, and often a question is relevant to more than one
group - as in this case - you can see how it is relevant to Functions, and
Misc, but also there might be a VBA solution. So posting to multiple groups
in such a way that any replies appear in all groups is a generally prefered
ettiquette.

Why do you have a problem with it? Others have recommended it as the right
way.

M :)

"Joerg Mochikun" <n...@email.address> wrote in message
news:h7o2i2$2kh$1...@daniel-new.mch.sbs.de...

[Jacob Skaria]

Sub Macro()
Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria

[Michelle]

No, Thanks, but WrapText is already on for these cells, it's just that the
row height is not adjusting automatically.

M

"Jacob Skaria" <Jacob...@discussions.microsoft.com> wrote in message
news:385CB93B-8AC8-43C6...@microsoft.com...

[Jacob Skaria]

Cells.SpecialCells(xlCellTypeFormulas).Rows.AutoFit

[Michelle]

Thanks but unfortunately that doesn't work either, it would if it were text,
but because it's coming from a VLOOKUP, for some reason it doesn't work.

I wonder if there's something I've overlooked?

M

"Jacob Skaria" <Jacob...@discussions.microsoft.com> wrote in message

news:ACBB82FC-C62E-4EA7...@microsoft.com...

[Dave Peterson]

First, working with merged cells is a real pain.

They don't behave nicely in lots of situations (autofitting row height is just
the tip of the iceberg!)

Second, excel doesn't keep track of what cells are changed because of a
calculation. So you'd have to look through all the cells with formulas which
could be a pain, too.

Third, there's no easy way to find merged cells.

That said...

Jim Rech wrote a macro called AutoFitMergedCellRowHeight:
http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05

This could be modified to tie into a worksheet_calculate event.

If you want to try...

This goes behind the worksheet that has the merged cells with formulas in it.

Option Explicit
Private Sub Worksheet_Calculate()

Dim myRng As Range
Dim myCell As Range

'define your range with the addresses of the
'merged cells formulas
'or give it a nice name
'Set myRng = Me.Range("a1,b3,c7")
'Set myRng = Me.Range("MyMergedCells")

'or look through all the formulas
Set myRng = Nothing
On Error Resume Next
Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'no formulas found
End If

On Error Resume Next
Application.EnableEvents = False
For Each myCell In myRng.Cells
If myCell.MergeArea.Cells.Count = 1 Then
'do nothing, not a merged cell
Else
Call AutoFitMergedCellRowHeight(ActCell:=myCell)
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

You'll want to use what's best for you in this portion:

'define your range with the addresses of the
'merged cells formulas
'or give it a nice name
'Set myRng = Me.Range("a1,b3,c7") 'Option 1
'Set myRng = Me.Range("MyMergedCells") 'Option 2

'or look through all the formulas 'Option 3 (next 4 lines)
Set myRng = Nothing
On Error Resume Next
Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

My preference would be to use the named range (select the cells and then
insert|name|define). Then I wouldn't be looking through all the formulas with
each calculation. (If you move cells to a new location (inserting/deleting rows
or columns), you'd have to adjust that list of addresses.)

You can comment the options you don't want--and uncomment the line(s) that you
want to use.

This portion does all the work. It goes in a General module (Insert|Module
inside the VBE).

Option Explicit
''based on Jim Rech's code
''http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight(ActCell As Range)
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActCell.MergeCells Then
With ActCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActCell.ColumnWidth
For Each CurrCell In .Cells
MergedCellRgWidth _
= CurrCell.ColumnWidth + MergedCellRgWidth
Next CurrCell
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Notice the comment in Jim's code:

''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.

--

Dave Peterson

[Michelle]

Thanks Dave. I have a working solution - really appreciate the time you
spent putting this together.

M

"Dave Peterson" <pete...@verizonXSPAM.net> wrote in message
news:4A9FBA8B...@verizonXSPAM.net...

[Ron Rosenfeld]

On Thu, 3 Sep 2009 11:29:17 +0100, "Michelle" <mh_lond...@hotmail.com>
wrote:

>Yes, I do mind. I get lots of stroppy replies if I post similar requests to
>more than one group, and often a question is relevant to more than one
>group - as in this case - you can see how it is relevant to Functions, and
>Misc, but also there might be a VBA solution. So posting to multiple groups
>in such a way that any replies appear in all groups is a generally prefered
>ettiquette.
>
>Why do you have a problem with it? Others have recommended it as the right
>way.
>
>M :)
>

Maybe he's confusing *cross-posting* (which you did and is proper) with
*multi-posting*, where you post separate messages to each of the NG's.
--ron

[Joerg Mochikun]

"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:4erv95d1s64mcpo5g...@4ax.com...

Yes, I did. Sorry and shame on me.
Joerg

[Unknown]

What was the work around you mentioned?
All I want is to get Excel to autoresize from VLOOKUP. I killed of merge cells opened a new file, rebuilt the thing and *still* stuck on the resizing issue.

What's the secret to just getting Excel to look at it's rows and go "oh look, this VLOOKUP returned 10 characters as opposed to 300; I need to resize". And visa versa. I've been at this for 5 hours with the most exciting part geting ONE, and I repeate, ONE cell to ever get bigger (not smaller) as its 'resize' macro/VB/whatever.

I need 40 rows, none merged, just to simply resize with the VLOOKUP returns that are in them.

Michelle wrote:

Thanks Dave.
03-Sep-09

Thanks Dave. I have a working solution - really appreciate the time you
spent putting this together.

M

Previous Posts In This Thread:

On Thursday, September 03, 2009 5:18 AM
Michelle wrote:

Resizing cells to fit text returned by VLOOKUP
I have some VLOOKUPs which sometimes return 50 or 60 characters (they are in

merged cells). I want the text to wrap, and the cell to enlarge to fit the
text - Just like it does whan you type it in, but somehow, because it is
being returned from a formula it just displays the bottom line of the

wrapped text, and does not resize the row.

Anyone know how I cam make it fit?

I am happy to use a VBA solution if there is no built in way to make it happen
automatically.

Thanks

M

On Thursday, September 03, 2009 5:33 AM
Joerg Mochikun wrote:

Would you mind to limit your request to ONE newsgroup?
Would you mind to limit your request to ONE newsgroup?
Joerg

On Thursday, September 03, 2009 6:29 AM
Michelle wrote:

Yes, I do mind.
Yes, I do mind. I get lots of stroppy replies if I post similar requests to
more than one group, and often a question is relevant to more than one
group - as in this case - you can see how it is relevant to Functions, and
Misc, but also there might be a VBA solution. So posting to multiple groups
in such a way that any replies appear in all groups is a generally prefered
ettiquette.

Why do you have a problem with it? Others have recommended it as the right
way.

M :)

On Thursday, September 03, 2009 7:09 AM
Jacob Skaria wrote:

Sub Macro()Cells.SpecialCells(xlCellTypeFormulas).

Sub Macro()
Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria

"Michelle" wrote:

On Thursday, September 03, 2009 7:26 AM
Michelle wrote:

No, Thanks, but WrapText is already on for these cells, it's just that the row

No, Thanks, but WrapText is already on for these cells, it is just that the

row height is not adjusting automatically.

M

On Thursday, September 03, 2009 7:41 AM
Jacob Skaria wrote:

Cells.SpecialCells(xlCellTypeFormulas).Rows.
Cells.SpecialCells(xlCellTypeFormulas).Rows.AutoFit

If this post helps click Yes
---------------
Jacob Skaria

"Michelle" wrote:

On Thursday, September 03, 2009 8:26 AM
Michelle wrote:

Thanks but unfortunately that doesn't work either, it would if it were text,

Thanks but unfortunately that does not work either, it would if it were text,
but because it is coming from a VLOOKUP, for some reason it does not work.

I wonder if there is something I have overlooked?

M

On Thursday, September 03, 2009 8:46 AM
Dave Peterson wrote:

First, working with merged cells is a real pain.
First, working with merged cells is a real pain.

They do not behave nicely in lots of situations (autofitting row height is just

the tip of the iceberg!)

Second, excel does not keep track of what cells are changed because of a
calculation. So you would have to look through all the cells with formulas which

could be a pain, too.

Third, there is no easy way to find merged cells.

That said...

End Sub

You'll want to use what is best for you in this portion:

'define your range with the addresses of the
'merged cells formulas
'or give it a nice name
'Set myRng = Me.Range("a1,b3,c7") 'Option 1
'Set myRng = Me.Range("MyMergedCells") 'Option 2

'or look through all the formulas 'Option 3 (next 4 lines)
Set myRng = Nothing
On Error Resume Next
Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

My preference would be to use the named range (select the cells and then

insert|name|define). Then I would not be looking through all the formulas with

each calculation. (If you move cells to a new location (inserting/deleting rows

or columns), you would have to adjust that list of addresses.)

You can comment the options you do not want--and uncomment the line(s) that you
want to use.

This portion does all the work. It goes in a General module (Insert|Module
inside the VBE).

Option Explicit
''based on Jim Rech's code
''http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight(ActCell As Range)
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActCell.MergeCells Then
With ActCell.MergeArea

On Thursday, September 03, 2009 10:14 AM
Michelle wrote:

Thanks Dave.

Thanks Dave. I have a working solution - really appreciate the time you
spent putting this together.

M

On Thursday, September 03, 2009 12:25 PM
Ron Rosenfeld wrote:

Re: Resizing cells to fit text returned by VLOOKUP
wrote:

Maybe he is confusing *cross-posting* (which you did and is proper) with

*multi-posting*, where you post separate messages to each of the NG's.
--ron

On Thursday, September 03, 2009 9:10 PM
Joerg Mochikun wrote:

Re: Resizing cells to fit text returned by VLOOKUP

Yes, I did. Sorry and shame on me.
Joerg

Submitted via EggHeadCafe - Software Developer Portal of Choice
Task Parallelism in C# 4.0 with System.Threading.Tasks
http://www.eggheadcafe.com/tutorials/aspnet/21013a52-fe11-4af8-bf8b-50cfd1a51577/task-parallelism-in-c-4.aspx

[Dave Peterson]

Excel doesn't change the size of rows/columns when a formula recalculates. I
would imagine that there would be people just as upset if their meticulously
designed forms changed with every recalculation.

But you could use the worksheet_calculate event to resize rows or columns.

Option Explicit
Private Sub Worksheet_Calculate()

application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

This code would be located in the worksheet's module that needs this behavior.

Rightclick on the worksheet tab, select view code, and paste this into the newly
opened code window (usually on the right).

Then back to excel and recalculate.

--

Dave Peterson

[mrsjam...@yahoo.com]

Did anybody ever respond to you. It's amazing that there isn't a simple solution posted. I'm looking for same solution. How to autofit the cell size from the information a vlookup returns.

[Nicholas Baldassaro]

12 years later... Dave Peterson saves the day. Thanks Dave!