Deleting columns based upon the value that appears in column A

[Steve]

Earlier today Tom Ogilvy offered me the following solution
for deleting all columns in my spreasheet other than those
that had "first name","surname" or "score" in row A.

Dim rng as Range, sStr as String, i as Long
set rng = cells(1,"IV").End(xltoLeft)
for i = rng.column to 1 step -1
sStr = lcase(cells(1,i).Value)
if sStr <> "first name" and _
sStr <> "surname" and _
sStr <> "score" then
cells(1,i).EntireColumn.Delete
end if
Next

The solution worked as I required it to, however, I have
run into problems when trying to adapt the same code on a
spreadsheet that has 84 columns of which I want to delete
all of them bar 7. When I re-run the macro (having change
the relevant strings) all columns are deleted.

Any ideas as to what may be wrong ? Again, row 1 is where
all the relevant information is stored.

Many thanks
Steve

 

[Tom Ogilvy]

Guess we would have to see what modifications you made.

 

[Steve]

I have now found the cause of this problem. I was not
using lower case letters when stating the field names that
I wanted to keep.

Having deleted all columns that are no longer required, is
it possible to re-order them based upon the values that
appear in row A ?

i.e. "Surname" appears as the first column, "First Name"
the second and "Score" the third, regardless of what order
they appeared in the original spreadsheet

 

[Tom Ogilvy]

I already gave you a solution to that.