none
How to delete multiple entire columns by column number? RRS feed

  • Question

  • Hi all,

    I would like to delete a range of columns.  I am attempting this (which fails):

    Dim First as Integer = 1
    Dim Last as Integer = 5
    objSheet.Columns(First:Last).entirecolumn.delete

    Another way to do this that works is to delete the rows one at a time until Last remains:

    For intDeleteCounter As Integer = FirstColumnNumber To LastColumnToDeleteNumber
    objSheet.Columns(FirstColumnNumber).entirecolumn.delete
    Next

    But it seems to me I should be able to delete a range (without Dimming a Range).

    Tuesday, July 17, 2018 3:50 PM

Answers

  • Another way to do this that works is to delete the rows one at a time until Last remains:
    For intDeleteCounter As Integer = FirstColumnNumber To LastColumnToDeleteNumber
    objSheet.Columns(FirstColumnNumber).entirecolumn.delete
    Next

    But it seems to me I should be able to delete a range (without Dimming a Range).

    It's a shame you can't delete by numeric range, but I found the above solution to be the simplest.  Since I know the first and last column by number, I simply deleted the rows one at a time iteratively. 

    It's probably not as efficient as deleting all the columns in one fell swoop, but since my program will probably never have more than 10 or so columns of data to deal with, this works well enough.

    Steve

    • Marked as answer by sesheldon Thursday, July 19, 2018 3:09 PM
    Thursday, July 19, 2018 3:09 PM

All replies

  • This should work:

    Private Sub DeleteColumnRange(workbookPath As String, workSheetName As String, firstColumnIndex As Integer, lastColumnIndex As Integer)
        Dim excel As New Microsoft.Office.Interop.Excel.Application
        Dim workbook = excel.Workbooks.Open(workbookPath)
        Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets.Item(workSheetName)
        Try
            Dim firstColumnAddress As String = CType(worksheet.Columns.Item(firstColumnIndex), Microsoft.Office.Interop.Excel.Range).Address(ReferenceStyle:=Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1)
            Dim lastColumnAddress As String = CType(worksheet.Columns.Item(lastColumnIndex), Microsoft.Office.Interop.Excel.Range).Address(ReferenceStyle:=Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1)
            worksheet.Range($"{firstColumnAddress}:{lastColumnAddress}").Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftToLeft)
            workbook.Save()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            workbook.Close()
            excel.Quit()
            Runtime.InteropServices.Marshal.ReleaseComObject(excel)
        End Try
    End Sub
    
    (PS. I thought there was a way to get column names from index without a messy algorithm, just didn't recall for sure.  This is it though... using the Address property on the column range)


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Proposed as answer by Alex Li-MSFT Wednesday, July 18, 2018 1:13 AM
    Tuesday, July 17, 2018 4:22 PM
    Moderator
  • Thanks again, Reed.

    Looks like you are deleting columns by alpha name rather than numeric designation.

    I believe you can also delete by numeric designation directly.  Just need to figure out the syntax.

    Steve

    Tuesday, July 17, 2018 5:09 PM
  • The Range method only takes A1-style naming, so that's why there is a lookup on the column index to get the column name.  If you just want one column you can use the index number directly, but since you want a range of columns (your question was about avoiding the loop over column numbers) you need to use the A1-style name.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, July 17, 2018 7:11 PM
    Moderator
  • Hi

    I think the reed method is quite good, you can also use the following methods:

    Private Sub DeleteColumnRange(workbookPath As String, workSheetName As String, firstColumnIndex As Integer, lastColumnIndex As Integer) Dim excel As New Microsoft.Office.Interop.Excel.Application Dim workbook = excel.Workbooks.Open(workbookPath) Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets.Item(workSheetName) Try For i = firstColumnIndex To lastColumnIndex CType(worksheet.Cells(firstColumnIndex, firstColumnIndex), Microsoft.Office.Interop.Excel.Range).Select() CType(worksheet.Cells(firstColumnIndex, firstColumnIndex), Microsoft.Office.Interop.Excel.Range).EntireColumn.Delete(0) Next workbook.Save() Catch ex As Exception MessageBox.Show(ex.Message) Finally workbook.Close() excel.Quit() Runtime.InteropServices.Marshal.ReleaseComObject(excel) End Try

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, July 18, 2018 2:35 AM
  • Another way to do this that works is to delete the rows one at a time until Last remains:
    For intDeleteCounter As Integer = FirstColumnNumber To LastColumnToDeleteNumber
    objSheet.Columns(FirstColumnNumber).entirecolumn.delete
    Next

    But it seems to me I should be able to delete a range (without Dimming a Range).

    It's a shame you can't delete by numeric range, but I found the above solution to be the simplest.  Since I know the first and last column by number, I simply deleted the rows one at a time iteratively. 

    It's probably not as efficient as deleting all the columns in one fell swoop, but since my program will probably never have more than 10 or so columns of data to deal with, this works well enough.

    Steve

    • Marked as answer by sesheldon Thursday, July 19, 2018 3:09 PM
    Thursday, July 19, 2018 3:09 PM
  • Steve,

    I guess I don't understand why you wouldn't simply convert the numeric range into the A1 style naming as I show in my example?  Then you get to delete the range of columns in one go and its only two lines of code to convert the column index into the column name.

    -EDIT-

    Did you understand that you would call the example method by passing column numbers?

    For example, to delete columns 3 though 7 from a given workbook:

    DeleteColumnRange("C:\user\documents\workbook1.xls", "Sheet1", 3, 7)


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Thursday, July 19, 2018 3:32 PM
    Moderator