Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFit Row Height
I have a row of merged cells, into which the user types comments.
These comments could be a few as 2 lines and up to as many as 12 lines. What I would like is a procedure I could run which will resize the height of the merged cell to just fir the amount of text entered. Range ("TargetRange").Rows.AutoFit works fine if the cells are not merged into one. However if the cells are merged andf the same line of code is run the cell collapes to just one line. is there a way to achieve what I require? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFit Row Height
You can play with 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 "PWS" wrote: I have a row of merged cells, into which the user types comments. These comments could be a few as 2 lines and up to as many as 12 lines. What I would like is a procedure I could run which will resize the height of the merged cell to just fir the amount of text entered. Range ("TargetRange").Rows.AutoFit works fine if the cells are not merged into one. However if the cells are merged andf the same line of code is run the cell collapes to just one line. is there a way to achieve what I require? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFit Row Height
On Feb 19, 1:55 pm, "PWS" wrote:
I have a row of merged cells, into which the user types comments. These comments could be a few as 2 lines and up to as many as 12 lines. What I would like is a procedure I could run which will resize the height of the merged cell to just fir the amount of text entered. Range ("TargetRange").Rows.AutoFit works fine if the cells are not merged into one. However if the cells are merged andf the same line of code is run the cell collapes to just one line. is there a way to achieve what I require? Hi Do the cells need to be merged? Row and Column Autofit don't work with merged cells (presumably because the system doesn't know what to do with the other cells in that row/column) You can Autofit unmerged cells though. with merged cells you would have to set the row height/column width. You can't get at the text length in units like cm (as far as I know) so you would have to count characters and so on....messy. regards Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFit Row Height
A workable if not precise solution.
This does not actually autosize to the exact require row height for reasons I do not understand. It seems that wrapped text that is a cell merged across 6 columns of width 10, does not wrap the same as a 60 wide single cell. Hence why in most cases the procedure below does not produce accurate results - which I need. Anyone else any ideas? On Feb 19, 3:15 pm, Tom Ogilvy wrote: You can play with 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 "PWS" wrote: I have a row of merged cells, into which the user types comments. These comments could be a few as 2 lines and up to as many as 12 lines. What I would like is a procedure I could run which will resize the height of the merged cell to just fir the amount of text entered. Range ("TargetRange").Rows.AutoFit works fine if the cells are not merged into one. However if the cells are merged andf the same line of code is run the cell collapes to just one line. is there a way to achieve what I require?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFit Row Height
The quick brown fox jumps over the lazy dog.The quick brown fox jumps
over the lazy dog.The quick brown fox jumps over the lazy dog.The quick brown fox jumps over the lazy dog.The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog.The quick brown fox jumps over the lazy dog.The quick brown fox jumps over the lazy dog.The quick brown fox jumps over the lazy dog.The quick brown fox jumps over the lazy dog. If you put the above text into a cell, set it to text wrap, and then autosize, it does not work accurately. Is there a way around this does ayone know. PWS |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFit Row Height
Glad you found it helpful. Your welcome.
-- Regards, Tom Ogilvy "PWS" wrote: A workable if not precise solution. This does not actually autosize to the exact require row height for reasons I do not understand. It seems that wrapped text that is a cell merged across 6 columns of width 10, does not wrap the same as a 60 wide single cell. Hence why in most cases the procedure below does not produce accurate results - which I need. Anyone else any ideas? On Feb 19, 3:15 pm, Tom Ogilvy wrote: You can play with 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 "PWS" wrote: I have a row of merged cells, into which the user types comments. These comments could be a few as 2 lines and up to as many as 12 lines. What I would like is a procedure I could run which will resize the height of the merged cell to just fir the amount of text entered. Range ("TargetRange").Rows.AutoFit works fine if the cells are not merged into one. However if the cells are merged andf the same line of code is run the cell collapes to just one line. is there a way to achieve what I require?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set a minimum row height a the same time with autofit row height | New Users to Excel | |||
Autofit Row Height | Excel Discussion (Misc queries) | |||
Autofit height | Excel Worksheet Functions | |||
Autofit - But At Least a Certain Height | Excel Discussion (Misc queries) | |||
Autofit Row Height | Excel Discussion (Misc queries) |