Hello Again,
Sorry I wrote the code to delete the column if match was found rather than
the other way.
But I would go with Bob's reply anyway
Sorry for the mixup on my part.
My code could be reversed easily by changing the "if z0 then..." to "if z=0
then.."

"ChasAA" wrote:

Hello
As Don stated in his reply, you must have STEP-1 to be looping down. In
fact you must loop down because it is better (in this case) to delete from
the right.

You cannot use Selection.EntireColumn.Delete as this will delete the column
wherever you current selected cell is.

Here are three versions of getting the shortest code.
I would use V1 or V2 as V3 is just silly. You could use it if you are
challenging yourself to write the least amount of lines, and with a bit of
lateral thinking get it even shorter.

You would add all your criteria to the assignment of criteriaString

[Code samples]
Sub DeleteColumnV1()
Dim criteriaString As String
Dim currentCell As String
Dim z As Integer
Dim i As Integer
criteriaString = "GMtURNOVERcONTRIBUTIONpAYMENTbALANCE"
For i = 10 To 1 Step -1
currentCell = Cells(1, i).Value
z = InStr(criteriaString, currentCell)
If z 0 And currentCell < "" Then
Columns(Cells(1, i).Column).Delete
End If
Next
End Sub

Sub DeleteColumnV2()
Dim criteriaString As String
Dim currentCell As String
Dim z As Integer
Dim i As Integer
criteriaString = "GMtURNOVERcONTRIBUTIONpAYMENTbALANCE"
For i = 10 To 1 Step -1
z = InStr(criteriaString, Cells(1, i))
If z 0 And Cells(1, i) < "" Then
Columns(Cells(1, i).Column).Delete
End If
Next
End Sub

Sub DeleteColumnV3()
For i = 10 To 1 Step -1
If (InStr("GMtURNOVERcONTRIBUTIONpAYMENTbALANCE", Cells(1, i))) And
(Cells(1, i) < "") Then
Columns(Cells(1, i).Column).Delete
End If
Next
End Sub

Hope this helps

ChasAA

"T De Villiers" wrote:


following is my code:

For i = 100 to 1

If cells(1,i) < "GM" AND If cells(1,i) < "tURNOVER" AND If cells(1,i)
< "cONTRIBUTION" AND............Then
Selection.EntireColumn.Delete

There are 15 criteria, how do I shorten my code, maybe using an array?
not sure.

Thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26479
View this thread: http://www.excelforum.com/showthread.php?threadid=566269