Hello,

i have an excel-file whith 2 sheets.
There are much columns, but I need only a few of them.
I want to delete some columns by name.

I found this code but, I don´t know why it doesn´t work!?!?
It tells me ".UsedRange" is wrong.

Can you help me, please?

(Reference "Microsoft Excel 16.0 Object Library" is already added in my project)

Option Strict On
Imports System.IO
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim Excel As Microsoft.Office.Interop.Excel.Application
        Dim xlWB As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWSTabelle1 As Excel.Worksheet = CType(CType(xlWB.Sheets("Tabelle1"), Excel.Worksheet), Excel.Worksheet)
        Dim xlWSTabelle2 As Excel.Worksheet = CType(CType(xlWB.Sheets("Tabelle2"), Excel.Worksheet), Excel.Worksheet)
        Dim Path = "D:\Test.xlsx"
        Excel = New Microsoft.Office.Interop.Excel.Application
        xlWB = Excel.Workbooks.Open(Path)

        Dim xlSheets As Object
        Dim xlSheetsArray(0 To 1) As Excel.Worksheet
        Dim k As Long
        Dim i As Long

        xlSheetsArray(0) = xlWSTabelle1
        xlSheetsArray(1) = xlWSTabelle2


        For Each xlSheets In xlSheetsArray

            With xlSheets

                k = .UsedRange.Columns.Count

                For i = k To 1 Step -1
                    Select Case LCase(.UsedRange.Cells(1, i).Value)

                        'Keep these columns
                        Case "#Num", "Product A", "Number 1" '...

                        Case Else

                            'Delete all others not listed above
                            .UsedRange.Columns(i).Delete()

                    End Select
                Next i

            End With

        Next xlSheets
    End Sub
End Class

As this is "found" code and not something you wrote, I'd try again but write it yourself. Or contact the author.

The error message is at line 40. "Option Strict On can not bind later" (or something like that, because I have to translate it by myself)

commented: To give everyone the best chance at solving, supply the message as-is in whatever language it is. +17

the message is : BC30574 "Option Strict On" lässt spätes Binden nicht zu.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.