View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Peter T Peter T is offline
external usenet poster
 
Posts: 5,600
Default Autofit on Merged Cells.

Hi Elena,

I can't see an obvious reason why the code would fail assuming
- CoverWs is referenced to a Worksheet
- strA1 is a valid address of a single cell
- no protection

Just glancing at the code I'm not sure the function will do quite what you
want. If .WrapText the cell(1) has probably autofitted the row height, so
when you come to autofit the column width it wouldn't reset to a single line
of text in the cell. But I might be missing something either in the code or
your overall objective.

Why not try it all in VBA, get it working as you want and without error
before re-adapting to .Net, and 'catch' any further errors.

Regards,
Peter T

wrote in message
ups.com...
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 found some code previously posted and changed the code to fit my
program. Now, I get an error on this line of code (full code below):
For Each CurrCell In CoverWs.Range(strA1).MergeArea

I believe the error is in the CurrCell as Excel.Range, but I'm not
sure.

And advice would be appreciated,
Thanks,
Elena


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