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.
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
User contributions licensed under CC BY-SA 3.0