CODEWITHSUNDEEP
vbaexcel
0m3r
0m3r

Reputation: 12499

How to delete columns by headers name

Error Type mismatch on following line

vMatch = Application.Match(vHeaders(i), Sheets(Sht).Rows(lColumn), 0)

When debug, code is showing vMatch = Empty what am I missing?

Sub TestArray()
    Dim vHeaders() As Variant
    Dim vMatch As Variant
    Dim i As Long
    Dim Sht As Worksheet
    Dim lColumn As Long

    Set Sht = ActiveWorkbook.Sheets("Data")

    lColumn = Sht.UsedRange.Columns.Count

    vHeaders = Array("Branch", "Account#", "Route Name", "Driver Number", _
                  "Reference2", "Reference3", "Stop Number", "Phone", "Delivery Time", _
                  "Stop Close Time", "POD Contact Name", "Latitude", "Longitude", _
                  "Status", "Service", "ASN Create Date", "ASN Date", "StopID", _
                  "Load Scan", "Delivery Scan", "Exception", "Exception Time")


    For i = LBound(vHeaders) To UBound(vHeaders) Step 1
        vMatch = Application.Match(vHeaders(i), Sheets(Sht).Rows(lColumn), 0)
        If IsNumeric(vMatch) Then Sheets(Sht).Columns(vMatch).delete
    Next i

End Sub

Upvotes: 1

Views: 462

Answers (1)

Sixthsense
Sixthsense

Reputation: 1975

Just replace this lines

    vMatch = Application.Match(vHeaders(i), Sheets(Sht).Rows(lColumn), 0)
    If IsNumeric(vMatch) Then Sheets(Sht).Columns(vMatch).delete

With

    vMatch = Application.Match(vHeaders(i), Sht.Rows(1), 0)
    If IsNumeric(vMatch) Then Sht.Columns(vMatch).Delete

Also no need for the below code and declaration

lColumn = Sht.UsedRange.Columns.Count

Upvotes: 3

Related Questions