Deleting columns based upon the value that appears in ROW A

[Steve]

Hi

I would like to delete columns in a spreadsheet dependant
upon the value in row a.

So for example the macro could say delete all columns
other than those that have "First Name", "Surname"
or "Score" in row a.

Is this possible ?

If so, can you tell me how to do it ?

Thanks in advance.
Steve

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.Deleting.html

 

[Tom Ogilvy]

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

 

[Steve]

Thanks guys.

Tom

That is exactly what I am looking for. So that i can use
it with other scenarios would you please explain which
part of the code tells me to look in row 1 only ?

One more thing...would it be possible to re-order the
columns ? i.e. "surname","first name" and "score" (in that
order) irrespective of the order they appear in the
original spreadsheet ?

Regards
Steve

 

[Tom Ogilvy]

Cells(1,"IV")

cells(1,i)

the 1 means row 1.

Sub AASetColumns()
Dim rng As Range, sStr As String, i As Long
Set rng = Cells(1, "IV").End(xlToLeft)
i = rng.Column
Do
sStr = LCase(Cells(1, i).Value)
If sStr <> "first name" And _
sStr <> "surname" And _
sStr <> "score" Then
Cells(1, i).EntireColumn.Delete

Else
Select Case sStr
Case "first name"
If i <> 2 Then
Cells(1, i).EntireColumn.Cut
Columns(2).Insert
i = i + 1
End If
Case "surname"
If i <> 1 Then
Cells(1, i).EntireColumn.Cut
Columns(1).Insert
i = i + 1
End If
Case "score"
If i <> 3 Then
Cells(1, i).EntireColumn.Cut
Columns(3).Insert
i = i + 1
End If
End Select
End If
i = i - 1
Loop While i > 0

End Sub