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

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

"Michelle" <mh_londonNOJUNK@hotmail.com> wrote in message 
news:44EB431F-4EDB-4AD1-98CF-774D61B59636@microsoft.com...
>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
> 

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" <no@email.address> wrote in message 
news:h7o2i2$2kh$1@daniel-new.mch.sbs.de...
> Would you mind to limit your request to ONE newsgroup?
> Joerg
>
> "Michelle" <mh_londonNOJUNK@hotmail.com> wrote in message 
> news:44EB431F-4EDB-4AD1-98CF-774D61B59636@microsoft.com...
>>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
>>
>
> 

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

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


"Michelle" wrote:

> 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
> 

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

M


"Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message 
news:385CB93B-8AC8-43C6-90F6-0389931FCE37@microsoft.com...
> Sub Macro()
> Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Michelle" wrote:
>
>> 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
>> 

Cells.SpecialCells(xlCellTypeFormulas).Rows.AutoFit

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


"Michelle" wrote:

> No, Thanks, but WrapText is already on for these cells, it's just that the 
> row height is not adjusting automatically.
> 
> M
> 
> 
> "Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message 
> news:385CB93B-8AC8-43C6-90F6-0389931FCE37@microsoft.com...
> > Sub Macro()
> > Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
> > End Sub
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Michelle" wrote:
> >
> >> 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
> >> 
> 

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" <JacobSkaria@discussions.microsoft.com> wrote in message 
news:ACBB82FC-C62E-4EA7-987D-9F3190E906C8@microsoft.com...
> Cells.SpecialCells(xlCellTypeFormulas).Rows.AutoFit
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Michelle" wrote:
>
>> No, Thanks, but WrapText is already on for these cells, it's just that 
>> the
>> row height is not adjusting automatically.
>>
>> M
>>
>>
>> "Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message
>> news:385CB93B-8AC8-43C6-90F6-0389931FCE37@microsoft.com...
>> > Sub Macro()
>> > Cells.SpecialCells(xlCellTypeFormulas).WrapText = True
>> > End Sub
>> >
>> > If this post helps click Yes
>> > ---------------
>> > Jacob Skaria
>> >
>> >
>> > "Michelle" wrote:
>> >
>> >> 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
>> >>
>> 

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.





Michelle wrote:
> 
> 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

-- 

Dave Peterson

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

M


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4A9FBA8B.E811ACEA@verizonXSPAM.net...
> 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.
>
>
>
>
>
> Michelle wrote:
>>
>> 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
>
> -- 
>
> Dave Peterson 

On Thu, 3 Sep 2009 11:29:17 +0100, "Michelle" <mh_londonNOJUNK@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

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:4erv95d1s64mcpo5gnae59stm0pm526juk@4ax.com...
> On Thu, 3 Sep 2009 11:29:17 +0100, "Michelle" 
> <mh_londonNOJUNK@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

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