1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

delete rows not matching multiple criteria

Discussion in 'Business Applications' started by jsimms001, Oct 15, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. jsimms001

    jsimms001 Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    3
    I am using an excel sheet and I need to delete any rows that do not match the criteria I need:

    688106
    midvale
    CITY TOTAL:
    7891234
    688123
    SINGLE 01



    In this column I would need to be able to automatically delete any row not containing SINGLE 01 or CITY TOTAL:
    I have tried several different macros and cannot seem to make one work.
    The actual column is 6000 rows long

    Any help???
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    When you say "Macro" do you mean a recorded macro or VBA Code?
    VBA can easily do this, but it is much easier if you start from the bottom of the data and work up.
    Which Column is the City total or Single 01 in?
     
  3. jsimms001

    jsimms001 Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    3
    VBA would be great. They are both in column D
    Thanks
    John
     
  4. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    John, could you attach a file with dummy data?
     
  5. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    John, try using the code below:

    Code:
    Sub Delete_Rows_ColD()
    
    Application.ScreenUpdating = False
    
            Dim Firstrow As Long
            Dim Lastrow As Long
            Dim Lrow As Long
    
            With ActiveSheet
    
            'Set the first and last row to loop through
            Firstrow = .UsedRange.Cells(1).Row 'The 1 represents the RowIndex
            Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
            'Loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
    
            'Check the values in the D column
            With .Cells(Lrow, "D")
    
                'This will delete each row that does NOT contain the 
                'multiple values "CITY TOTAL:" & "SINGLE 01" in Column D, [B]case sensitive.[/B]
                If .Value <> "CITY TOTAL:" And .Value <> "SINGLE 01" Then .EntireRow.Delete
    
            End With
            Next Lrow
    
        End With
    
    Application.ScreenUpdating = True
    
    End Sub
    
    A more experience Excel user may be able to provide you with a better code, but I believe this will fit your needs.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    That looks good to me, just what I had in mind. (y):D
     
  7. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hey Cman, thanks for the nice descriptors of what each piece of code does. Slowly trying to learn it, as you know, and that helps a lot! (y)
     
  9. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    No problem. I am still trying to learn myself.

    Zack has really helped me learn to code within Excel.
     
  10. jsimms001

    jsimms001 Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    3
    When I use this code it deltes everything..is there something else I need to address??
    Thanks for your help!!!
    John
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    These lines of code are very specific
    With .Cells(Lrow, "D")
    If .Value <> "CITY TOTAL:" And .Value <> "SINGLE 01" Then .EntireRow.Delete

    The value must be CITY TOTAL: or SINGLE 01 (in all uppercase) and it must be in D.

    As an alternative, you could try (replacing what is in the "With" routine)
    If (.Cells(Lrow, "D").Value <> "CITY TOTAL:") And _
    (.Cells(Lrow, "D").Value <> "SINGLE 01") _
    ( Then .Rows(Lrow).Delete
    End If
     
  12. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Slurpee, that is a great idea. Good work. :)
     
  13. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    John, are you able to attach a copy of the workbook with dummy data?
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Danke...slow but sure.... :D
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759436

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice