VBA: Delete Column if header row reads certain value

[mrkevelev]

I will be opening CSV files that will be exported from another location. Some of the data columns are useless that comes through, so I want to delete them if the title contains specific text. I don't just want to delete the column based on location (i.e. delete column D regardless of what the title is), but rather look for the title name, and once found, delete that entire column. The reason being is that the exported CSV file will not necessarily contain the same column ordering, because it depends on the user's preferences at the time of exporting the CSV file in different software. There are more than 1 of these columns, so maybe if I could enter a list of the titles that I need to remove that would be helpful. So let's say reading across the first row it reads: "Item" in cell A1, "Quantity" in cell B1, "Cost" in C1, and "Vendor" in D1, and I want to delete the data columns for Quantity and Vendor, I would just need to adjust the macro to say the list (i.e. ..."Quantity", "Vendor"...).

 

[mumps]

Try this macro. Just add the headers of the columns you want to delete to the array.

Sub deleteCols()
    Application.ScreenUpdating = False
    Dim colArr As Variant, i As Long, fnd As Range
    colArr = Array("Quantity", "Vendor")
    For i = LBound(colArr) To UBound(colArr)
        Set fnd = Rows(1).Find(colArr(i), LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            Columns(fnd.Column).Delete
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[mrkevelev]

Very nice, thanks a lot!

 

[mumps]

You are very welcome.