Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Wrap Text in Merged Cells (Office 97)

    Hi

    Can anyone tell me, it there is a way to wrap text in merged cells?

    Thanks in advance

    Braddy

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    Format cells- "alignment" tab, check "Wrap Text"

    Adjust row height as necessary (auto row size is "disabled" with merged cells)

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Wrap Text in Merged Cells (Office 97)

    Hi Steve

    Thanks for the reply, I think I might be after the impossible, I wanted it to grow automaticaly as it I add text to the merged cells!

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Wrap Text in Merged Cells (Office 97)

    Hi Steve

    Sorry I am a bit dumb, I do not know what a VB worksheet object is!

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    open excel
    go to VB (alt-F11)
    go to proj explorer (ctrl-r)
    Insert - module
    dbl-click the new module (module 1, if you had none previously) and get module 1 in the macro code pane (on the right)
    Copy the subroutine from the webpage I listed and paste it into the macro code pane

    Let's assume you want to autofit cell A1 of sheet 1:
    Dbl-click in the proj exploer window the "folder object" that says sheet 1
    Paste the code I listed in the macro code window (worksheet change code)

    Now whenever you make a change to the worksheet, this macro will be run. If the cell you changed is NOT cell A1, it does nothing. if you change cell A1
    the portion in the IF is done and and cell A1 is adjusted.

    Change A1 to the appropriate cell. You could even use A1:A100 or even A:A for the entire column or A:C for multiple columns.

    alt-Q will close VB Editor

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Wrap Text in Merged Cells (Office 97)

    Hi Steve

    Thanks for the idiot sheet I have managed to get it to work by manually running the macro, but I am struggling to get it to autorun,

    I have also tried to adjust the text in the vb to (decrease)as you suggested but I am not quite sure how to write it.

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    1) did you follow the directions as I outlined using the 2 macros exactly as written: code from web page in the module, code I gave in a worksheet object?

    2) Did you merge cell A1 on that sheet with some other cells A1, B1, C1, etc and wrap text in the cells?

    After doing above, if you edit cell A1 it should work.

    After getting this to work, to modify the code to shrink as well as expand, do what I detailed:
    <hr>change the line:
    .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    CurrentRowHeight, PossNewRowHeight)

    to:
    .RowHeight = PossNewRowHeight
    <hr>


    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Wrap Text in Merged Cells (Office 97)

    Hi Steve

    Works fine now.

    Grateful thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    The referenced page seems to only have a picture of the code.

    Does anyone have it already typed in?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    Edited by HansV to update link.

    Here is some code which can do it.
    http://www.freelists.org/archives/ms.../msg00008.html
    It works by determining the col width of the merged area, unmerging it, setting the first col to that width, and deteriming the row height required to autofit it

    It then resets the col width and the merge and puts in the new row height.

    To make it auto will require adding it to a worksheet change event linked to the proper cell(s)

    Something like this (in a VB worksheet object) to call the routine in the webpage above.

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    Target.Select
    AutoFitMergedCellRowHeight
    End If
    End Sub</pre>


    NOTE: the code in the website will ONLY EXPAND the range it will NOT shrink it if the text decreases.
    You could change the line:
    .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    CurrentRowHeight, PossNewRowHeight)

    to:
    .RowHeight = PossNewRowHeight

    to decrease it also if desired.

    Steve

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    I am trying to use this code. I have a spread sheet where rows 1 and 2 are merged, rows 3 and 4 are merged etc. I write in colum E that has word wrap but when I type in it , the rows do not expand. I copied the macro but it doesn't work. Maybe I am not using it right.
    I put the following in a module in my Personal spreadsheet. It would really be helpful if this worked. It doesn't have to be automatic.

    Sub AutoFitMergedCellRowHeight()
    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    Dim CurrCell As Range
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    If ActiveCell.MergeCells Then
    With ActiveCell.MergeArea
    If .Rows.Count = 1 And .WrapText = True Then
    Application.ScreenUpdating = False
    CurrentRowHeight = .RowHeight
    ActiveCellWidth = ActiveCell.ColumnWidth
    For Each CurrCell In Selection
    MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
    Next
    .MergeCells = False
    .Cells(1).ColumnWidth = MergedCellRgWidth
    .EntireRow.AutoFit
    PossNewRowHeight = .RowHeight
    .Cells(1).ColumnWidth = ActiveCellWidth
    .MergeCells = True
    .RowHeight = PossNewRowHeight
    End If
    End With
    End If
    End Sub

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    What doesn't seem to work?

    Note: The macro is not automatic.
    You have to select the appropriate cells then run the macro.

    If you want it automatic you have to write an event macro to call it like is listed in <post#=270923>post 270923</post#>. This macro selects the cells that have been changed and then calls the macro.

    Steve

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    That code is for horizontally merged cells, it doesn't work for vertically merged cells. Here is a version for vertically merged cells:

    Sub AutoFitMergedCellRowHeight()
    Dim MergedCellRgHeight As Single
    Dim CurrCell As Range
    Dim PossNewRowHeight As Single
    Dim lngRowCount As Long
    If ActiveCell.MergeCells Then
    With ActiveCell.MergeArea
    If .Columns.Count = 1 And .WrapText = True Then
    lngRowCount = .Rows.Count
    Application.ScreenUpdating = False
    For Each CurrCell In Selection
    MergedCellRgHeight = CurrCell.RowHeight + MergedCellRgHeight
    Next
    .MergeCells = False
    .Cells(1).RowHeight = MergedCellRgHeight
    .EntireRow.AutoFit
    PossNewRowHeight = .Cells(1).RowHeight
    .MergeCells = True
    For Each CurrCell In Selection
    CurrCell.RowHeight = PossNewRowHeight / lngRowCount
    Next
    End If
    End With
    End If
    End Sub

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    Works fine. Thanks

  15. #15
    New Lounger
    Join Date
    Feb 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrap Text in Merged Cells (Office 97)

    Does anyone have a way for making the AutoFitMergeCells code work for a merged range that includes both multiple rows and multiple columns?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •