|
Auto size help
I have a feature in my workbook that produces a report view on one worksheet
by copying certain data fields into labelled cells. One receiving cell is comprised of merged cells - when it receives text/data only some of it is visible as the cell is too small - tried several things to no avail. Is there a for the labelled cell to auto fit the the received data or is this limited by excel? Tia |
Auto size help
Merged cells don't autofit. You might look at this code previously posted
by Jim Rech: Jim Rech 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 -- Regards, Tom Ogilvy "sacrum" wrote in message ... I have a feature in my workbook that produces a report view on one worksheet by copying certain data fields into labelled cells. One receiving cell is comprised of merged cells - when it receives text/data only some of it is visible as the cell is too small - tried several things to no avail. Is there a for the labelled cell to auto fit the the received data or is this limited by excel? Tia |
Auto size help
Hi Tom,
I'm having rpoblems with the script.... My worksheet has a sub called Form which extracts choice and put onto form. The merged cells are in worksheet set as wrep and are labelled RepRiskDetail Would you kindly help me put that context into the script you posted? Ideally I would want this function within the sub form. Chris "Tom Ogilvy" wrote in message ... Merged cells don't autofit. You might look at this code previously posted by Jim Rech: Jim Rech 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 -- Regards, Tom Ogilvy "sacrum" wrote in message ... I have a feature in my workbook that produces a report view on one worksheet by copying certain data fields into labelled cells. One receiving cell is comprised of merged cells - when it receives text/data only some of it is visible as the cell is too small - tried several things to no avail. Is there a for the labelled cell to auto fit the the received data or is this limited by excel? Tia |
Auto size help
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Range Dim ActiveCellWidth As Single, PossNewRowHeight As Single ' add this line: Range("RepRiskDetail").Select 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 -- regards, Tom Ogilvy "sacrum" wrote in message ... Hi Tom, I'm having rpoblems with the script.... My worksheet has a sub called Form which extracts choice and put onto form. The merged cells are in worksheet set as wrep and are labelled RepRiskDetail Would you kindly help me put that context into the script you posted? Ideally I would want this function within the sub form. Chris "Tom Ogilvy" wrote in message ... Merged cells don't autofit. You might look at this code previously posted by Jim Rech: Jim Rech 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 -- Regards, Tom Ogilvy "sacrum" wrote in message ... I have a feature in my workbook that produces a report view on one worksheet by copying certain data fields into labelled cells. One receiving cell is comprised of merged cells - when it receives text/data only some of it is visible as the cell is too small - tried several things to no avail. Is there a for the labelled cell to auto fit the the received data or is this limited by excel? Tia |
Auto size help
Thanjks Tom - all is working well now.
"Tom Ogilvy" wrote in message ... Sub AutoFitMergedCellRowHeight() Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Range Dim ActiveCellWidth As Single, PossNewRowHeight As Single ' add this line: Range("RepRiskDetail").Select 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 -- regards, Tom Ogilvy "sacrum" wrote in message ... Hi Tom, I'm having rpoblems with the script.... My worksheet has a sub called Form which extracts choice and put onto form. The merged cells are in worksheet set as wrep and are labelled RepRiskDetail Would you kindly help me put that context into the script you posted? Ideally I would want this function within the sub form. Chris "Tom Ogilvy" wrote in message ... Merged cells don't autofit. You might look at this code previously posted by Jim Rech: Jim Rech 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 -- Regards, Tom Ogilvy "sacrum" wrote in message ... I have a feature in my workbook that produces a report view on one worksheet by copying certain data fields into labelled cells. One receiving cell is comprised of merged cells - when it receives text/data only some of it is visible as the cell is too small - tried several things to no avail. Is there a for the labelled cell to auto fit the the received data or is this limited by excel? Tia |
| All times are GMT +1. The time now is 09:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com