Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Number of Rows for Merged Cell Text Wrap
I have lengthy string that I want to display in entirety. I would like to
pick how many cells wide to merge and based on that, have Excel calculate for me how many rows I need. How is this done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Number of Rows for Merged Cell Text Wrap
You can specify the width of the column, set it to wordwrap then use autofit
and it will put the correct number of rows. "George Lee" wrote: I have lengthy string that I want to display in entirety. I would like to pick how many cells wide to merge and based on that, have Excel calculate for me how many rows I need. How is this done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Number of Rows for Merged Cell Text Wrap
Here is some more info from Jim Rech about merged cells and autofit.
Unfortunately autofit ignores merged cells. The only workaround I know of is a macro that simulates autofit for row heights. Of course it's not automatic unless you call it from the worksheet change event. ''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() 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 = IIf(CurrentRowHeight PossNewRowHeight, _ CurrentRowHeight, PossNewRowHeight) End If End With End If End Sub -- Jim Rech Excel MVP "George Lee" wrote: I have lengthy string that I want to display in entirety. I would like to pick how many cells wide to merge and based on that, have Excel calculate for me how many rows I need. How is this done? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Number of Rows for Merged Cell Text Wrap
I found that page and have used it. Quite good. This was one of those
functions that seems it should have been atomic to Excel. Thanks. "JLGWhiz" wrote: Here is some more info from Jim Rech about merged cells and autofit. Unfortunately autofit ignores merged cells. The only workaround I know of is a macro that simulates autofit for row heights. Of course it's not automatic unless you call it from the worksheet change event. ''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() 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 = IIf(CurrentRowHeight PossNewRowHeight, _ CurrentRowHeight, PossNewRowHeight) End If End With End If End Sub -- Jim Rech Excel MVP "George Lee" wrote: I have lengthy string that I want to display in entirety. I would like to pick how many cells wide to merge and based on that, have Excel calculate for me how many rows I need. How is this done? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Number of Rows for Merged Cell Text Wrap
This works well for autofitting with content. But if I clear the content of
the cell, the row does not shrink to fit the empty cell (in other words, revert to the default row height). Is there code you can provide to do this? Thanks in advance. "JLGWhiz" wrote: Here is some more info from Jim Rech about merged cells and autofit. Unfortunately autofit ignores merged cells. The only workaround I know of is a macro that simulates autofit for row heights. Of course it's not automatic unless you call it from the worksheet change event. ''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() 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 = IIf(CurrentRowHeight PossNewRowHeight, _ CurrentRowHeight, PossNewRowHeight) End If End With End If End Sub -- Jim Rech Excel MVP "George Lee" wrote: I have lengthy string that I want to display in entirety. I would like to pick how many cells wide to merge and based on that, have Excel calculate for me how many rows I need. How is this done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merged cell text does not wrap | Excel Discussion (Misc queries) | |||
rows of text in merged cell with wrap text | Excel Programming | |||
Wrap Text in Merged Cell | Excel Programming | |||
Rows with merged cells are not adjusting even w/ Wrap Text and au. | Excel Discussion (Misc queries) | |||
How do i wrap text in a merged cell in excell? | Excel Programming |