Inserting multiple rows into an Excel Sheet with Vb.Net Com Add-In

Question

After determining a row index for an Excel Worksheet, InsertRow, I would like to insert 16 rows after it using VB.net. I am building a COM add-in if that changes anything. In VBA I would do this via:

 ...
 Dim InsertRow as String
 ...
 Dim ContractForm As Worksheet
 Set ContractForm = Sheets("Lab Contracts")
   ContractForm.Select
   ContractForm.Rows(InsertRow & ":" & InsertRow).Select
   Range(Selection, Selection.Offset(8, 0)).EntireRow.Insert

I can't seem to find a way to do this in VB.net. I've tried: [for all instances of (InsertRow ":" InsertRow) I've also tried ("47:47") and (InsertRow) in case that's not the correct syntax (vice versa).

  ...
  ContractSheet.Rows(InsertRow ":" InsertRow).Select()
  ContractSheet.Rows.Insert()
  ...

and ContractSheet.Rows("47:47").Select() ContractSheet.Range("47:47").EntireRow.Insert() and ContractSheet.Rows(InsertRow).Select() For n = 1 To 16 ContractSheet.Range(InsertRow & ":" & InsertRow).rows.insert(xlDown) Next and ContractSheet.Rows(InsertRow).Select() For n = 1 To 16 ContractSheet.Selection.Insert() Next

and I could go on, but I don't think it would be helpful (as I've always been trying for more than an hour on one issue before asking here). Depending on how it goes I get one of the following errors:

HResult: 0x800A03EC; or

HResult 0x80020005; or

Cannot sift Objects off sheet.

Any help would be greatly appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 11:49

It's best to avoid Select statements unless you have to and you don't have to here. So, just to re-lay the groundwork, in VBA you should do:

Sub test()
Dim InsertRow As Long
Dim ContractForm As Worksheet

InsertRow = "5"
Set ContractForm = Sheets("Lab Contracts")
ContractForm.Rows(InsertRow).Resize(8).Insert xlShiftDown
End Sub

In VB.Net it's almost the same. (And I guess the xlShiftDown argument is unneeded in either VB or VBA):

Imports Microsoft.Office.Interop

Sub test()
    Dim ContractForm As Excel.Worksheet
    Dim InsertRow As Int32

    ContractForm = Application.ActiveWorkbook.Worksheets("Lab Contracts")
    InsertRow = 5
    ContractForm.Rows(InsertRow).Resize(8).insert() '(Excel.XlInsertShiftDirection.xlShiftDown)
End Sub

Awesome thanks so much! I google around and was reading up on Rows on MSDN, and somehow never saw Resize. I don't know how I missed that one. I had figgured out from my own trial and error to avoid select, I just didn't know it was possible in this case. — Atl LED, Mar 23 '13 at 04:34

Inserting multiple rows into an Excel Sheet with Vb.Net Com Add-In

1 Answers1