VB.NET excel deleting multiple columns at the same time

0

I am trying to delete more than one column in my excel sheet.

    For Each lvi In ListView1.Items
        If lvi.Checked = True Then
            arrayLetters = lvi.SubItems(1).Text & ":" & lvi.SubItems(1).Text & "," & arrayLetters
        End If
    Next

    arrayLetters = arrayLetters.Substring(0, arrayLetters.Length - 1)

    Dim rg As Excel.Range = xlSheet.Columns(arrayLetters)
    rg.Select()
    rg.Delete()

The value of arrayLetters is "G:G,F:F". For some reason that doesn't seem to work once it gets there to delete them! Only reason i am doing it this way is so that it doesn't update the table and loop to the other one. In other words, if i delete each one individually then the column moves and the letter will not be the same the next go around.

The error is on the Dim rg As Excel.Range = xlSheet.Columns(arrayLetters) line and it says:

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Any help would be great!

David

SOLVED

 For Each lvi In ListView1.Items
    If lvi.Checked = False Then
        arrayLetters = lvi.SubItems(2).Text & "," & arrayLetters 'Puts numbers in BACKWORDS
    End If
 Next

 arrayLetters = arrayLetters.Substring(0, arrayLetters.Length - 1)

 Dim theNumbers As String() = arrayLetters.Split(",")
 Dim num As Integer = 0

 xlApp.ScreenUpdating = False

 For Each num In theNumbers
     xlSheet.Columns(num).delete() 'Deletes columns in reverse order (7,5,4...)
 Next
vb.net
visual-studio-2010
excel
asked on Stack Overflow Feb 25, 2011 by StealthRT • edited Feb 25, 2011 by StealthRT

3 Answers

1

Just delete the lowest numbered column N number of times to reflect how many columns in a row you want to delete. It's better to go off of column numbers rather than letters when dealing with Excel programatically. If you need a code example, let me know and I'll post one.

Edit:

Here is a code example that does what you want:

xlSheet.Columns(i).delete
answered on Stack Overflow Feb 25, 2011 by Davido • edited Feb 25, 2011 by Davido
1

You want

xlSheet.Range(arrayLetters)

I reckon, that is Range, not Column.

answered on Stack Overflow Feb 25, 2011 by Fionnuala
0

I think the important part to realize (which I didn't initially) is that you have to use numbers for the columns not the letters.

answered on Stack Overflow Oct 27, 2015 by John Roa

User contributions licensed under CC BY-SA 3.0