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...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566269

you need the step if going from the bottom up. You don't need the IF more
than once.You don't need to select.
for i=100 to 1 step-1
if cells(1,i)<"GM" and cells(1,i) < "tURNOVER" then
cells(1,i).entirecolumn.delete
next i

Are you trying to delete more than one column? If not, then use FIND to find
the column to delete. You could use select case for lots of criteria

--
Don Guillett
SalesAid Software

"T De Villiers"
wrote in message
news:T.De.Villiers.2bpig2_1154176206.1429@excelfor um-nospam.com...

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...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566269

This requires a list of your 'Criteria' somewhere in the workbook and the
list has a Name. In my example it is "Crit_List".
This works on column A and finds how long the list is in case it is not 100.
This puts your list into myArray and compares every cell in column A for a
Match to the list. If no Match is found (If IsError(Application.Match) then
the row is deleted. This also works from the bottom, up.

Sub Filter()
Dim myArray As Variant
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
myArray = ThisWorkbook.Names("Crit_List").RefersToRange.Valu e

FirstRow = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If IsError(Application.Match(Cells(iRow, "A").Value, myArray, 0))
Then
Rows(iRow).Delete
Else
'do nothing
End If
Next iRow
End Sub

Mike F

"T De Villiers"
wrote in message
news:T.De.Villiers.2bpig2_1154176206.1429@excelfor um-nospam.com...

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...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566269

Dim i As Long
Dim aryTest

aryTest = Array("GM", "Turnover", "Contribution", "etc") '<=== Update

For i = 100 To 1 Step -1
If Not IsError(Application.Match(Cells(1, i).Value, aryTest, 0))
Then
Columns(i).Delete
End If
Next i


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"T De Villiers"
wrote in message
news:T.De.Villiers.2bpig2_1154176206.1429@excelfor um-nospam.com...

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...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566269

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

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

Thanks for all this - very helpfu

--
T De Villier
-----------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647
View this thread: http://www.excelforum.com/showthread.php?threadid=56626