How to delete Excel Rows on Vb.net

[jared.geli]

Hi guys I need help in deleting rows in Excel. What I want is if I delete a row in DataGridView it will also delete a row with the same value. I tried using the OleDb way and I find out I can't delete a row using that so I tried using the Interop way still no luck, worse the excel file I used can't be close. It says "Object variable or With block variable not set". After I reboot my laptop it didn't delete the row

xlWorkBook = xlApp.Workbooks.Open(path)

        xlWorkSheet.DeleteRow(b)

        xlWorkBook.Save()

        xlWorkBook.Close()

[Minimalist]

The error means that you haven't instanciated your workbook. The folowing is I think the way to do it:

'Instantiate a Workbook object.
'Load a template file.
Dim workbook As Workbook = New Workbook("d:\test\MyBook.xls")

'Get the first worksheet in the book.
Dim sheet As Worksheet = workbook.Worksheets(0)

'Insert 10 rows at row index 2 (insertion starts at 3rd row)
sheet.Cells.InsertRows(2, 10)

'Delete 5 rows now. (8th row - 12th row)
sheet.Cells.DeleteRows(7, 5)

'Save the excel file.
workbook.Save("d:\test\out_MyBook.xls")

[jared.geli]

I tried your code now it showing me an exception something about retrieving COM failed. I tweeked your code a bit to my preference. I used the openfiledialog to get the path of the excel file

 Dim a = DataGridView1.CurrentRow.Cells(0).Value

        Dim b As Integer = a + 2

        Dim c = ComboBox1.Text

        'xlWorkBook = xlApp.Workbooks.Open(path)

        Dim workbook As Excel.Workbook = New Excel.Workbook(path)

        If ComboBox1.Text = "MAM" Then

            Dim sheet As Excel.Worksheet = workbook.Worksheets(2)

            sheet.Cells.DeleteRows(b)

        End If

[jared.geli]

Update: This time it's showing Public member 'Deleterows' on type 'Range' not found.

Dim a = DataGridView1.CurrentRow.Cells(0).Value

        Dim b As Integer = a + 2

        Dim c = ComboBox1.Text

        xlWorkBook = xlApp.Workbooks.Open(path)

        'Dim workbook As Excel.Workbook = New Excel.Workbook(path)

        If ComboBox1.Text = "MAM" Then

            xlWorkSheet = xlWorkBook.Worksheets(2)

            xlWorkSheet.Rows(b).Deleterows()

        End If 

        xlWorkBook.Save()

        xlWorkBook.Close()

[jared.geli]

Also if possible how can I make the rows shift up after the delete

[jared.geli]

Problem solved on the delete and shift up now what I want is to delete a row according to their row number. If I manually input the row number it's working but if I use some variables it deletes the first row only my column headers. The values of DataGridView1.CurrentRow.Cells(0).Value is less 2 of the row header because my headers occupy the 1st and 2nd rows and my data starts at 3rd row

Dim a As Integer = DataGridView1.CurrentRow.Cells(0).Value

        Dim b As Integer = a + 2

        Dim c = ComboBox1.Text

        xlWorkBook = xlApp.Workbooks.Open(path)

        'Dim workbook As Excel.Workbook = New Excel.Workbook(path)

        If ComboBox1.Text = "MAM" Then

            xlWorkSheet = xlWorkBook.Worksheets(3)

            xlWorkSheet.Cells(b).EntireRow.Delete()

        End If

        xlWorkBook.Save()

        xlWorkBook.Open()

[jared.geli]

Problem solved. I just indicate the number of rows to be deleted

I just changed this part of code:

 xlWorkSheet.Cells(b).EntireRow.Delete()

To this:

 xlWorkSheet.Cells(b, 1).EntireRow.Delete()