+ Reply to Thread
Results 1 to 7 of 7

Multiple Criteria - Shortening Code

  1. #1
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171

    Multiple Criteria - Shortening Code

    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

  2. #2
    Don Guillett
    Guest

    Re: Multiple Criteria - Shortening Code

    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
    [email protected]
    "T De Villiers" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >




  3. #3
    Mike Fogleman
    Guest

    Re: Multiple Criteria - Shortening Code

    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.Value

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >




  4. #4
    Bob Phillips
    Guest

    Re: Multiple Criteria - Shortening Code

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >




  5. #5
    ChasAA
    Guest

    RE: Multiple Criteria - Shortening Code

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


  6. #6
    ChasAA
    Guest

    RE: Multiple Criteria - Shortening Code

    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 z>0 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
    > >
    > >


  7. #7
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    Thanks for all this - very helpful

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1