Autofit on Merged Cells.

[iereinoso]

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

 

[Peter T]

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

 

[Tom Ogilvy]

I found some code previously posted and changed the code to fit my
program.

The code was that of Jim Rech's which is pretty well proven.

 

[Peter T]

I misinterpreted the objective and should have recognized the code as Jim
Rech's.

Elena, there is indeed an error/typo in the code you posted, change

.EntireRow.AutoFit()

to
..EntireRow.AutoFit

I see you are disabling Screenupdating. I assume you ensure it is reset
somewhere, either further down in the routine or in a calling routine.

Regards,
Peter T

 

[Elena]

Hi Gentlemen,

The () after the autofit is automatic to the VB.NET. If I remove the
() it adds it back in automatically.

I know the code is well proven, but for VBA. I'm trying to adapt it
for VB.NET.

One thing... strA is a valid cell... it's "a" & whatever row. Yet, it
is after a merge. So the code overlaps cells a:j.

I'll try it in VBA.

The error is focusing on the CurrCell. Do I have to define the range
at some point? Is there a step I'm missing?

Tom, you helped me with this code originally in the msdn site. I
posted a reply, but one has replied.

Thanks so much for you help!
~Elena

 

[Elena]

It works perfectly in VBA. I just have to find a way to adapt it to
VB.NET that calls Excel.