Loop through column titles (1st row) if name is in array

[FuNeS13]

I know I'm doing something wrong in this part of the code, but I don't know what...

Dim MyArray As Variant
Dim found As Boolean
Dim uB As Integer, lB As Integer
Dim LC As Long

MyArray = Array("Type", "Number", "Order", "Invoice Date", "Due/Paid Date", "Amount", "Shipment", "Customer", "Name", "Credit Limit", "Chk/Ref")
LC = Cells(1, Columns.Count).End(xlToLeft).Column '56 columns
     uB = UBound(MyArray)
    lB = LBound(MyArray)
c = 1

For i = lB To uB
        Do Until c = LC + 1
                If Range(Split(Cells(1, c).Address, "$")(1) & "1").Value = MyArray(i) Then
                    found = True
                Else
                    found = False
                End If
                
                If found = False Then
                    Range(Split(Cells(1, c).Address, "$")(1) & ":" & Split(Cells(1, c).Address, "$")(1)).Select
                    Selection.Delete Shift:=xlToLeft
                Else
                GoTo nexti
                End If
        c = c + 1
        Loop
nexti:
Next i

what I want to do is if the titles on the first row exist in my array list, then I want to keep that column, if it doesn't exist then I want the column to be removed...

 

[Akuini]

what I want to do is if the titles on the first row exist in my array list, then I want to keep that column, if it doesn't exist then I want the column to be removed...

Try this code:

Sub FuNeS13()

Dim c As Range, f As Range
Dim myHeader As String
myHeader = "|Type|Number|Order|Invoice Date|Due/Paid Date|Amount|Shipment|Customer|Name|Credit Limit|Chk/Ref|"

For Each f In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
    If InStr(1, myHeader, "|" & f.Value & "|", vbTextCompare) = 0 Then
        If Not c Is Nothing Then
            Set c = Union(c, f)
        Else
            Set c = f
        End If
    End If
Next

c.EntireColumn.Delete

End Sub

 

[FuNeS13]

Try this code:

Sub FuNeS13()

Dim c As Range, f As Range
Dim myHeader As String
myHeader = "|Type|Number|Order|Invoice Date|Due/Paid Date|Amount|Shipment|Customer|Name|Credit Limit|Chk/Ref|"

For Each f In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
    If InStr(1, myHeader, "|" & f.Value & "|", vbTextCompare) = 0 Then
        If Not c Is Nothing Then
            Set c = Union(c, f)
        Else
            Set c = f
        End If
    End If
Next

c.EntireColumn.Delete

End Sub

thanks, this worked exactly as I was expecting it... would you mind explaining it a little bit to me?

 

[FuNeS13]

Another way is to iterate through each cell in row 1, use the MATCH function to check for existence in MyArray. If it doesn't exist, change the value to an "error".
Then, use SpecialCells to highlight the column, and afterwards perform a single delete operation (in the code, use .select for verification, and if it's fine, replace it with .delete).

Dim LC As Long, myArray, ce As Range
LC = Cells(1, Columns.Count).End(xlToLeft).Column
myArray = Array("Type", "Number", "Order", "Invoice Date", "Due/Paid Date", "Amount", "Shipment", "Customer", "Name", "Credit Limit", "Chk/Ref")
For Each ce In Range("A1", Cells(1, LC))
    If Not IsNumeric(Application.Match(ce, myArray, 0)) Then
        ce.Value = "#N/A"
    End If
Next
Range("A1", Cells(1, LC)).SpecialCells(xlCellTypeConstants, xlErrors).EntireColumn.Select
'Range("A1", Cells(1, LC)).SpecialCells(xlCellTypeConstants, xlErrors).EntireColumn.Delete

thank you, I also tried this solution and it worked as expected... I think your approach I understand a bit more than the previous one.