Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit in Merge Cells
I am writing a program in VB.NET that populates an Excel spreadsheet.
I know that it is not possible to have autoheight or autofit on merged cells. I need the height of the row to expand to fit an undetermined amount of text. I wonder if anyone has code to get around this problem. I need to find a way to get around this problem and fast. Anyone found a way around it? Thanks in Advance, Elena |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit in Merge Cells
Hi Elena,
Try the following code from Jim Rech: '============= Public Sub AutoFitMergedCellRowHeight() '---------------------------------------------- '\\ Jim RECH http://tinyurl.com/esbrx '\\ This macro does an autofit of row heights on merged cells: '\\ 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. '---------------------------------------------- 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, Norman "Elena" wrote in message ... I am writing a program in VB.NET that populates an Excel spreadsheet. I know that it is not possible to have autoheight or autofit on merged cells. I need the height of the row to expand to fit an undetermined amount of text. I wonder if anyone has code to get around this problem. I need to find a way to get around this problem and fast. Anyone found a way around it? Thanks in Advance, Elena |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit in Merge Cells
Thanks, Norman.
I'm going to try and manipulate it to work in VB.NET. ~Elena "Norman Jones" wrote: Hi Elena, Try the following code from Jim Rech: '============= Public Sub AutoFitMergedCellRowHeight() '---------------------------------------------- '\\ Jim RECH http://tinyurl.com/esbrx '\\ This macro does an autofit of row heights on merged cells: '\\ 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. '---------------------------------------------- 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, Norman "Elena" wrote in message ... I am writing a program in VB.NET that populates an Excel spreadsheet. I know that it is not possible to have autoheight or autofit on merged cells. I need the height of the row to expand to fit an undetermined amount of text. I wonder if anyone has code to get around this problem. I need to find a way to get around this problem and fast. Anyone found a way around it? Thanks in Advance, Elena |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit in Merge Cells
I changed the code, but VB.NET gives two errors:
Dim CurrCell As Range - 'System.Data.Range' is not accessible in this context beacuse it is 'Private' For Each CurrCell In Selection - error states "Selection is not Declared" Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Range Dim ActiveCellWidth As Single, PossNewRowHeight As Single If CoverWs.Range(strA1).MergeCells Then With CoverWs.Range(strA1).MergeArea If .Rows.Count = 1 And .WrapText = True Then ThisApplication.ScreenUpdating = False CurrentRowHeight = .RowHeight ActiveCellWidth = CoverWs.Range(strA1).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 If I declare the Selection, how do I then define it? I'm not sure about the other error either. Thanks so much, Elena "Elena" wrote: Thanks, Norman. I'm going to try and manipulate it to work in VB.NET. ~Elena "Norman Jones" wrote: Hi Elena, Try the following code from Jim Rech: '============= Public Sub AutoFitMergedCellRowHeight() '---------------------------------------------- '\\ Jim RECH http://tinyurl.com/esbrx '\\ This macro does an autofit of row heights on merged cells: '\\ 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. '---------------------------------------------- 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, Norman "Elena" wrote in message ... I am writing a program in VB.NET that populates an Excel spreadsheet. I know that it is not possible to have autoheight or autofit on merged cells. I need the height of the row to expand to fit an undetermined amount of text. I wonder if anyone has code to get around this problem. I need to find a way to get around this problem and fast. Anyone found a way around it? Thanks in Advance, Elena |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit in Merge Cells
try changing
For Each CurrCell In Selection to For Each CurrCell In CoverWs.Range(strA1).MergeArea Maybe it will clear up both errors. -- Regards, Tom Ogilvy "Elena" wrote: I changed the code, but VB.NET gives two errors: Dim CurrCell As Range - 'System.Data.Range' is not accessible in this context beacuse it is 'Private' For Each CurrCell In Selection - error states "Selection is not Declared" Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Range Dim ActiveCellWidth As Single, PossNewRowHeight As Single If CoverWs.Range(strA1).MergeCells Then With CoverWs.Range(strA1).MergeArea If .Rows.Count = 1 And .WrapText = True Then ThisApplication.ScreenUpdating = False CurrentRowHeight = .RowHeight ActiveCellWidth = CoverWs.Range(strA1).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 If I declare the Selection, how do I then define it? I'm not sure about the other error either. Thanks so much, Elena "Elena" wrote: Thanks, Norman. I'm going to try and manipulate it to work in VB.NET. ~Elena "Norman Jones" wrote: Hi Elena, Try the following code from Jim Rech: '============= Public Sub AutoFitMergedCellRowHeight() '---------------------------------------------- '\\ Jim RECH http://tinyurl.com/esbrx '\\ This macro does an autofit of row heights on merged cells: '\\ 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. '---------------------------------------------- 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, Norman "Elena" wrote in message ... I am writing a program in VB.NET that populates an Excel spreadsheet. I know that it is not possible to have autoheight or autofit on merged cells. I need the height of the row to expand to fit an undetermined amount of text. I wonder if anyone has code to get around this problem. I need to find a way to get around this problem and fast. Anyone found a way around it? Thanks in Advance, Elena |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit in Merge Cells
I changed the code and now get an error on this line of code (full code
below): For Each CurrCell In CoverWs.Range(strA1).MergeArea Error: An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll Additional information: Member not found. Current Code: Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Excel.Range Dim ActiveCellWidth As Single, PossNewRowHeight As Single If CoverWs.Range(strA1).MergeCells Then With CoverWs.Range(strA1).MergeArea If .Rows.Count = 1 And .WrapText = True Then ThisApplication.ScreenUpdating = False CurrentRowHeight = .RowHeight ActiveCellWidth = CoverWs.Range(strA1).ColumnWidth For Each CurrCell In CoverWs.Range(strA1).MergeArea 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 Thanks again, Elena "Tom Ogilvy" wrote: try changing For Each CurrCell In Selection to For Each CurrCell In CoverWs.Range(strA1).MergeArea Maybe it will clear up both errors. -- Regards, Tom Ogilvy "Elena" wrote: I changed the code, but VB.NET gives two errors: Dim CurrCell As Range - 'System.Data.Range' is not accessible in this context beacuse it is 'Private' For Each CurrCell In Selection - error states "Selection is not Declared" Dim CurrentRowHeight As Single, MergedCellRgWidth As Single Dim CurrCell As Range Dim ActiveCellWidth As Single, PossNewRowHeight As Single If CoverWs.Range(strA1).MergeCells Then With CoverWs.Range(strA1).MergeArea If .Rows.Count = 1 And .WrapText = True Then ThisApplication.ScreenUpdating = False CurrentRowHeight = .RowHeight ActiveCellWidth = CoverWs.Range(strA1).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 If I declare the Selection, how do I then define it? I'm not sure about the other error either. Thanks so much, Elena "Elena" wrote: Thanks, Norman. I'm going to try and manipulate it to work in VB.NET. ~Elena "Norman Jones" wrote: Hi Elena, Try the following code from Jim Rech: '============= Public Sub AutoFitMergedCellRowHeight() '---------------------------------------------- '\\ Jim RECH http://tinyurl.com/esbrx '\\ This macro does an autofit of row heights on merged cells: '\\ 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. '---------------------------------------------- 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, Norman "Elena" wrote in message ... I am writing a program in VB.NET that populates an Excel spreadsheet. I know that it is not possible to have autoheight or autofit on merged cells. I need the height of the row to expand to fit an undetermined amount of text. I wonder if anyone has code to get around this problem. I need to find a way to get around this problem and fast. Anyone found a way around it? Thanks in Advance, Elena |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofit (Columns.EntireColumn.AutoFit) does not work | Excel Discussion (Misc queries) | |||
Autofit and merge | Excel Worksheet Functions | |||
Merging Cells and autofit | Excel Discussion (Misc queries) | |||
Row Autofit on Merged Cells | Excel Discussion (Misc queries) | |||
Autofit in Merged Cells? | Excel Discussion (Misc queries) |