Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    Question Unanswered: Parse through records, deleting based on criteria...

    A query returns a spreadsheet containing Work Order information. I want to exlude Items that have been sent outside for machining, but only if the machining was performed in the 2nd operation. I have attached a sample spreadsheet, as the results of this query are best described by example.

    the last column contains the Operation Type (OPTYPE). If the second line of a work order has "OUT" for the OPTYPE, I want to delete that record: Work Order 55158 has OPTYPE=OUT in the second row (or second op, row 15) for that work order - I wan't to delete this work order (delete rows 14, 15, 16). Work Order 56947 would not be deleted because the "OUT" Operation is in the 4th line (or 4th operation).

    after deleting these work orders, I want to go through and delete all of the blank rows after a work order (rows 3-4, 6-9, 11-13, etc.).

    Can this be done with a macro? Any idea's on how to at least get started? I have some limited VB experience. Any help is appreciated!

    Thanks,
    Keith
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Posts
    106

    Do... Loop

    I would use a Do...Loop statement.

    For example:

    Dim myRow as Integer

    myRow = 1

    Do
    myRow = myRow + 1
    If Cells(1, 8).Text = "OUT" Then


    Loop Until myRow = 1000 'number of rows you want to check

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    I would use a Do...Loop statement.

    For example:

    Dim myRow as Integer

    myRow = 1

    Do
    myRow = myRow + 1
    If Cells(1, 8).Text = "OUT" Then
    Cells(1, 8).EntireRow.Delete
    End If
    Loop Until myRow = 1000 'number of rows you want to check

    This gives the general code for you to play with and you can work out the specifics yourself.

    sugarflux

    PS. I accidentally pressed Tab and enter when i was half way through my response so ignore the previous reply!

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Keith

    Im going to expand on what sugarflux has said.
    To find the order out in each sucssive row afte the initial work order then just do he following.

    Range("G2").select ' the first row with a work order in it
    do until activecell.row > 65000 ' this indicates the end of file
    if activecell.offset(1,1).formula = "OUT" then
    activecell.entirerow.delete
    end if
    activecell.end(xldown).select
    loop

    'to clear all the rows after the inital order line
    dim Lastrow as Double
    dim MyRange as String
    lastrow = range("H1").end(xldown).row ' determine the lastrow with data
    MyRange = "A1:A" & Lastrow 'select the first column down to the lastrow without data
    range(myrange).specialcells(xlcelltypeblanks).enti rerow.delete
    Range("A1").select

    just copy and paste the abovecode into a module
    and it should work quickly

    Hope This is ok for you
    David

  5. #5
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47
    Thanks!

    Your suggestions have been very helpful.

Posting Permissions

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