Wrap Text in Merged Cells (Office 97)

Topic

New Reply

Hi

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

Thanks in advance

Braddy

Reply | Quote

Replies

Format cells- “alignment” tab, check “Wrap Text”

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

Steve

Reply | Quote

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

Reply | Quote

Edited by HansV to update link.

Here is some code which can do it.
http://www.freelists.org/archives/mso/10-2003/msg00008.html%5B/url%5D
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.

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

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

Reply | Quote

Hi Steve

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

Braddy

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:
[indent]

---

change the line:
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)

to:
.RowHeight = PossNewRowHeight

---

[/indent]

Steve

Reply | Quote

Hi Steve

Works fine now.

Grateful thanks

Braddy

Reply | Quote

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

Does anyone have it already typed in?

Reply | Quote

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

Reply | Quote

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. This macro selects the cells that have been changed and then calls the macro.

Steve

Reply | Quote

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

Reply | Quote

Works fine. Thanks

Reply | Quote

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

Reply | Quote

Would you like to expand the rows or the columns?

Steve

Reply | Quote

Excel is not really good at this, so you mileage may vary:

Sub AutoFitMergedCellRowHeight()
Dim MergedHeight As Single
Dim MergedWidth As Single
Dim PossNewRowHeight As Single
Dim lngRowCount As Long
Dim lngColCount As Long
Dim i As Long
Dim ActiveCellWidth As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .WrapText = True Then
lngRowCount = .Rows.Count
lngColCount = .Columns.Count
Application.ScreenUpdating = False
MergedHeight = Selection.Height
For i = 1 To lngColCount
MergedWidth = .Cells(1, i).ColumnWidth + 1 + MergedWidth
Next i
ActiveCellWidth = ActiveCell.ColumnWidth
.MergeCells = False
.Cells(1).RowHeight = MergedHeight
.Cells(1).ColumnWidth = MergedWidth
.EntireRow.AutoFit
PossNewRowHeight = .Cells(1).RowHeight
.MergeCells = True
.Cells(1).ColumnWidth = ActiveCellWidth
For i = 1 To lngRowCount
.Cells(i, 1).RowHeight = PossNewRowHeight / lngRowCount
Next i
End If
End With
End If
End Sub

Reply | Quote

Hans,

Thank you for the code. I see what you mean about Excel not being good at text wrapping. There seems to a limit on the amount of text Excel can handle before it loses the ability to wrap text. I was trying to Merge cells in larger groups than I usually use. Using my usual method, which is breaking down the Merged cells into smaller groups. I’ve found that text either gets truncated or a line gets added when you print. What you see is not what you get. Larger cell mergers and auto fitting still do not solve the problem. I find that I still have to spend time Print Previewing and manually adjusting heights to get the print to look the way I want.

Excel is not a great tool for creation of forms featuring large text entries. But Excel is much better than Word at combining text form several different cells based on special criteria. I use many different blocks of text and combing them in different ways based on what I need the result to be. I know that Access is supposed to be the tool to use for what I’m doing but I have too much invested into my Excel forms to make the conversion at this point in my business life. Excel was there a long time before Access was.

Reply | Quote

HI. Hans pointed me to this discussion after I had a text wrap question. I had created a file in OpenOffice and then saved it in .xls format and was surprised that Excel did not show the wrapped merged cells properly. Anyhow, OOo seems to be more capable at wrapping text than Excel but, if you have to export it back to Excel format, the same problems are still there. If the file is for personal use, perhaps OOo would help solve your difficulties.

Of course, it would probably introduce difficulties of its own.

Reply | Quote