+ Reply to Thread
Results 1 to 12 of 12

Delete rows based on multiple criteria

Hybrid View

ohkking Delete rows based on multiple... 04-13-2011, 06:37 PM
TMS Re: Delete rows based on... 04-13-2011, 06:47 PM
ohkking Re: Delete rows based on... 04-14-2011, 10:18 AM
ohkking Re: Delete rows based on... 04-14-2011, 10:19 AM
jwright650 Re: Delete rows based on... 04-14-2011, 10:21 AM
TMS Re: Delete rows based on... 04-14-2011, 10:53 AM
Krishnakumar Re: Delete rows based on... 04-14-2011, 11:17 AM
jwright650 Re: Delete rows based on... 04-14-2011, 11:36 AM
Krishnakumar Re: Delete rows based on... 04-14-2011, 12:05 PM
ohkking Re: Delete rows based on... 04-18-2011, 11:07 AM
Krishnakumar Re: Delete rows based on... 04-18-2011, 11:23 AM
jwright650 Re: Delete rows based on... 04-18-2011, 11:30 AM
  1. #1
    Registered User
    Join Date
    04-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Delete rows based on multiple criteria

    Hi everyone,

    I'm trying to go through 685 rows of data and deleting any that don't have specific numbers in column A. I've never used macros before but through lots of Googling and experimenting I've come up with this macro:

    Sub Delete_Rows2()
    Dim cell As Range
    Dim J As Integer
    J = 685

    For i = 2 To 685


    Range(Cells(2, 1), Cells(J, 1)).Select
    For Each cell In Selection
    If cell <> "030247" _
    And cell <> "030248:030258" _
    And cell <> "030346" _
    And cell <> "030347" _
    And cell <> "030348" _
    And cell <> "030353" _
    And cell <> "030354" _
    And cell <> "030357" _
    And cell <> "030358" _
    And cell <> "030359" _
    And cell <> "030360" _
    And cell <> "030627" _
    And cell <> "030632" _
    And cell <> "030644" _
    And cell <> "030856" Then
    cell.EntireRow.Select
    Selection.Delete

    J = J - 1
    End If
    Next cell

    Next i

    End Sub


    This does exactly what I need, however I need about 100 "And" statements and Excel won't let me add many more. Is there other syntax that I could use to allow more options or do I have to break this into multiple macros/sets of if/and statements?

    Thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,501

    Re: Delete rows based on multiple criteria

    I'd be inclined to put all the values you are checking for into a separate sheet somewhere and then do a VLOOKUP or a COUNTIF. If it finds it, then leave the cell/row alone; if it doesn't, delete it.

    Incidentally, when deleting rows, it is better to work from the bottom up.

    A sample workbook with some typical data and before and after scenarios would help paint the picture and allow a more concrete proposal to be presented.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Delete rows based on multiple criteria

    I'm not familiar with either code, but it would make it a lot easier if I could just go to another sheet and say if it doesn't have any of these numbers in the first column, then delete the row. I attached a workbook with before, the list of numbers i want to keep, and after.

    The numbers that I'd be sorting out can change regularly so it would be awesome if I could just compare it to a list in excel and not have to edit the macro every time.

    What do you think, any chance of this happening?

  4. #4
    Registered User
    Join Date
    04-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Delete rows based on multiple criteria

    forgot to attach
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: Delete rows based on multiple criteria

    Try attaching the workbook again...it didn't make it.

    whoops....nevermind, it made it.
    Last edited by jwright650; 04-14-2011 at 10:22 AM. Reason: computer too slow refreshing
    Life is like a roll of toilet paper. The closer it gets to the end, the faster it goes.
    John Wright

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,501

    Re: Delete rows based on multiple criteria

    This, maybe:

    Sub Delete_Rows2()
    Dim i As Long
    
    Dim AWF As WorksheetFunction: Set AWF = Application.WorksheetFunction
    
    Application.ScreenUpdating = False
    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If AWF.CountIf(Worksheets("Numbers to Keep").Columns(1), Range("A" & i).Value) = 0 Then
            Range("A" & i).EntireRow.Delete
        End If
    Next 'i
    Application.ScreenUpdating = True
    
    End Sub

    Regards

  7. #7
    Forum Contributor
    Join Date
    02-19-2005
    Location
    Gurgaon,India
    MS-Off Ver
    2007,2010,2013
    Posts
    180

    Re: Delete rows based on multiple criteria

    Hi,

    Try

    Sub kTest()
        
        Dim ka, k(), a, i As Long, c As Long, n As Long, dic  As Object
        
        '// user settings
        Const strSourceData         As String = "Before"            'Sheet Name
        Const strNumsToKeep         As String = "Numbers to Keep"   'Sheet Name
        Const strDataColumns        As String = "a:i"
        Const strNumbersRange       As String = "a1:a24"
        Const lngNumColInData       As Long = 1
        Const strDestRange          As String = "k1"
        '// end of settings
        
        
        With Worksheets(CStr(strSourceData))
            ka = Intersect(.UsedRange, .Columns(CStr(strDataColumns)))
        End With
        
        With Worksheets(CStr(strNumsToKeep))
            a = Intersect(.UsedRange, .Range(CStr(strNumbersRange)))
        End With
        
        Set dic = CreateObject("scripting.dictionary")
            dic.comparemode = 1
            
        For i = 1 To UBound(a, 1): dic.Item(CStr(a(i, 1))) = Empty: Next
        
        ReDim k(1 To UBound(ka, 1), 1 To UBound(ka, 2))
        
        For i = 1 To UBound(ka, 1)
            If dic.exists(CStr(ka(i, lngNumColInData))) Then
                n = n + 1
                For c = 1 To UBound(ka, 2): k(n, c) = ka(i, c): Next
            End If
        Next
        
        If n Then Worksheets(CStr(strSourceData)).Range(CStr(strDestRange)).Resize(n, UBound(ka, 2)).Value2 = k
        
    End Sub

    HTH
    Last edited by Krishnakumar; 04-14-2011 at 11:19 AM.
    Kris

  8. #8
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: Delete rows based on multiple criteria

    Hi Krishnakumar,
    I'd love to know how your code works...that is awsome.
    What part of that would you have to change to make it place the copied data on the "after" sheet instead of the "before" sheet for the OP?

  9. #9
    Forum Contributor
    Join Date
    02-19-2005
    Location
    Gurgaon,India
    MS-Off Ver
    2007,2010,2013
    Posts
    180

    Re: Delete rows based on multiple criteria

    Hi,

    replace

    Worksheets(CStr(strSourceData)).Range(CStr(strDestRange))
    with

    Worksheets("After").Range(CStr(strDestRange))

  10. #10
    Registered User
    Join Date
    04-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Delete rows based on multiple criteria

    Thank you! Now, what would I change to have the results print on the "After" sheet starting in the A column instead of the K column? I've tried even changing all of the k's to b's to see if I could move it to column b and then I get an error that this line:

    For i = 1 To UBound(a, 1)

    is out of range. Why would it give me an error if all I did was change the k's to b's?

  11. #11
    Forum Contributor
    Join Date
    02-19-2005
    Location
    Gurgaon,India
    MS-Off Ver
    2007,2010,2013
    Posts
    180

    Re: Delete rows based on multiple criteria

    Hi,

    The k's are variable not column. The only change you need to do is replace "k1" with "a1" in the following line

    Const strDestRange          As String = "k1"

    HTH

  12. #12
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: Delete rows based on multiple criteria

    Here is his code in a workbook....
    Attached Files Attached Files

+ 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