If header title is not in the array then delete column?

[dataoli]

As the title would suggest i want to remove any columns that do not match an array. I tried the following code but receive an error, does anybody have a possible solution?

    Dim arr As Variant
    arr = Array("Title", "Forename", "Surname", "Address 1", "Address 2", "Address 3", "Address 4", "Town", "County", "Postcode")
    Set arrRange = RptSht.Range(RptSht.Cells(1, 1), RptSht.Cells(1, 10))
    For Each cell In arrRange
        If IsError(Application.Match(cell.Value, arr, 0)) Then
            cell.EntireColumn.Delete
        End If
    Next

I also tried using column numbers but I couldn't get it to work either!

Dim ColNum As Long, arr As Variant
    arr = Array(1, 2, 3, 4, 7, 8, 9, 10, 11, 12,13)
    For ColNum = LstCol To 1 Step -1
        If RptSht.Columns(ColNum) <> arr Then
            RptSht.Columns(ColNum).Delete
        End If
    Next

 

[steve the fish]

What is the error you receive?

 

[My Aswer Is This]

Looks like the same question posted here:
https://www.mrexcel.com/forum/excel-questions/1067800-simplify-code.html

 

[dataoli]

Thanks for the replies guys

Apologies I thought the question varied enough to warrant its own thread, I also wasn't sure on the rules about posting multiple questions in the same thread.

The codes run through 300+ headings but the first only removes about 6 columns then stops, the second code skips over completely.

 

[steve the fish]

You could try like this. Obviously assign RptSht first:

Dim arr As Variant, rng As Range
arr = Array("Title", "Forename", "Surname", "Address 1", "Address 2", "Address 3", "Address 4", "Town", "County", "Postcode")
Set arrRange = RptSht.Range(RptSht.Cells(1, 1), RptSht.Cells(1, 10))

For Each cell In arrRange
    If IsError(Application.Match(cell.Value, arr, 0)) Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, cell)
        Else
            Set rng = cell
        End If
    End If
Next
If Not rng Is Nothing Then rng.EntireColumn.Delete Shift:=xlToLeft

 

[dataoli]

Thank you steve the fish, it works a treat.