VB.NET excel deleting multiple columns at the same time

Question

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

Davido · Accepted Answer · 2011-02-25 16:10:34Z

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

edited Feb 25, 2011 at 16:10

answered Feb 25, 2011 at 16:03

Davido

2,93326 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

StealthRT Over a year ago

I have it set up as A-Z then AA-AZ, BB-BZ, etc. so it would be nice for me to be able to use that. However, i did try your code and it works for the first pass but error on the second...

Davido Over a year ago

Just keep in mind, that column(1) is column A. Also, as Remou posted, you might want to try getting the Range. Another thing to try is: Columns("F:G").Delete

StealthRT Over a year ago

I used you're code but still did the arrayLetters but only got the colum numbers instead of letters and ORDERED THEM BACKWORDS so that it would not change the order of the columns on each delete.

Fionnuala · Accepted Answer · 2011-02-25 16:04:27Z

1

You want

xlSheet.Range(arrayLetters)

I reckon, that is Range, not Column.

answered Feb 25, 2011 at 16:04

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Comments

John Roa · Accepted Answer · 2015-10-27 15:37:30Z

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 Oct 27, 2015 at 15:37

John Roa

611 silver badge7 bronze badges

Collectives™ on Stack Overflow

VB.NET excel deleting multiple columns at the same time

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related