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? |
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? |
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? |
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? |
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? |
All times are GMT +1. The time now is 10:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com