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

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


-----Original Message-----
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
.

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

--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
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

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
-----Original Message-----
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

--
Regards,
Tom Ogilvy

"Steve" wrote in
message
...
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


.

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

--
Regards,
Tom Ogilvy



"Steve" wrote in message
...
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
-----Original Message-----
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

--
Regards,
Tom Ogilvy

"Steve" wrote in
message
...
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


.