none
delete multi-row in excel (VB.NET) RRS feed

  • Question

  • How to delete multi-row (eg, code shown in VBA)in excel (VB.NET)? Thanks!

       
        Rows("1:31").Select
        Selection.Delete Shift:=xlUp

    Friday, August 30, 2019 9:30 AM

Answers

  • Hello,

    Doing this with Excel automation

    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class ExcelOperations
        Public Sub ExcelRemoveRow(FileName As String, SheetName As String, StartRow As Integer, EndRow As Integer)
    
            Dim Proceed As Boolean = False
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlRange1 As Excel.Range = Nothing
    
            xlApp = New Excel.Application With {
                .DisplayAlerts = False
            }
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For workSheetIndex As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(workSheetIndex), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            If Proceed Then
                For index As Integer = StartRow To EndRow
                    xlRange1 = CType(xlWorkSheet.Rows(index), Excel.Range)
                    xlRange1.Delete()
                    Marshal.FinalReleaseComObject(xlRange1)
                    xlRange1 = Nothing
                Next
    
                xlWorkSheet.SaveAs(FileName)
    
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseExcelObject(xlRange1)
            ReleaseExcelObject(xlWorkSheets)
            ReleaseExcelObject(xlWorkSheet)
            ReleaseExcelObject(xlWorkBook)
            ReleaseExcelObject(xlWorkBooks)
            ReleaseExcelObject(xlApp)
    
            GC.Collect()
    
        End Sub
        Private Sub ReleaseExcelObject(excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    
    End Class
    I generally avoid Excel automation for .xlsx files and use either SpreadSheetLight (totally free on NuGet) or GemBox SpreadSheet (not free). If not fixed on using Excel automation check out the following page mixed with free and paid for NuGet Excel packages


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, August 30, 2019 11:38 AM
    Moderator

All replies

  • Hello,

    Doing this with Excel automation

    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class ExcelOperations
        Public Sub ExcelRemoveRow(FileName As String, SheetName As String, StartRow As Integer, EndRow As Integer)
    
            Dim Proceed As Boolean = False
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlRange1 As Excel.Range = Nothing
    
            xlApp = New Excel.Application With {
                .DisplayAlerts = False
            }
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For workSheetIndex As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(workSheetIndex), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            If Proceed Then
                For index As Integer = StartRow To EndRow
                    xlRange1 = CType(xlWorkSheet.Rows(index), Excel.Range)
                    xlRange1.Delete()
                    Marshal.FinalReleaseComObject(xlRange1)
                    xlRange1 = Nothing
                Next
    
                xlWorkSheet.SaveAs(FileName)
    
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseExcelObject(xlRange1)
            ReleaseExcelObject(xlWorkSheets)
            ReleaseExcelObject(xlWorkSheet)
            ReleaseExcelObject(xlWorkBook)
            ReleaseExcelObject(xlWorkBooks)
            ReleaseExcelObject(xlApp)
    
            GC.Collect()
    
        End Sub
        Private Sub ReleaseExcelObject(excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    
    End Class
    I generally avoid Excel automation for .xlsx files and use either SpreadSheetLight (totally free on NuGet) or GemBox SpreadSheet (not free). If not fixed on using Excel automation check out the following page mixed with free and paid for NuGet Excel packages


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, August 30, 2019 11:38 AM
    Moderator
  • Thanks!
    Monday, September 2, 2019 2:30 AM