(VBA) Filter 14 different words, and delete rows that don't contain those words

Thanks Thanks:  0
Results 1 to 5 of 5

Thread: (VBA) Filter 14 different words, and delete rows that don't contain those words

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default (VBA) Filter 14 different words, and delete rows that don't contain those words

     
    Hi Everyone,

    This seems difficult.

    I'm trying to delete rows if the text in cells in column B is not equal to specific registrations, examples being:

    - ABC111
    - ABC112
    - ABC123
    - ABC124
    - ABC125
    - ABC126
    - ABC127
    - ABC128
    - ABC129
    - ABC130
    - ABC131
    - ABC132
    - ABC133
    - ABC134

    The registrations above need to be kept and any others deleted.


    Any help would be greatly appreciated!
    Last edited by Animalised; Aug 18th, 2016 at 12:38 PM.

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    12,891
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: (VBA) Filter 14 different words, and delete rows that don't contain those words

    Try :-
    Code:
    Sub MG18Aug19
    Dim Rng As Range, Dn As Range, n As Long
    Dim Lst As Long, Dic As Object
    Application.ScreenUpdating = False
    Lst = Range("B" & Rows.Count).End(xlUp).Row
    Set Dic = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
    Dic.Add "ABC111", ""
    Dic.Add "ABC112", ""
    Dic.Add "ABC123", ""
    Dic.Add "ABC124", ""
    Dic.Add "ABC125", ""
    Dic.Add "ABC126", ""
    Dic.Add "ABC127", ""
    Dic.Add "ABC128", ""
    Dic.Add "ABC129", ""
    Dic.Add "ABC130", ""
    Dic.Add "ABC131", ""
    Dic.Add "ABC132", ""
    Dic.Add "ABC133", ""
    Dic.Add "ABC134", ""
    
    For n = Lst To 1 Step -1
    With Range("B" & n)
        If Not Dic.Exists(.Value) Then .EntireRow.Delete
    End With
    Next
    Application.ScreenUpdating = True
    End Sub
    Regards Mick
    Last edited by MickG; Aug 18th, 2016 at 01:05 PM.

  3. #3
    New Member
    Join Date
    Apr 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (VBA) Filter 14 different words, and delete rows that don't contain those words

    Hi Mick,

    This worked a treat! You're brilliant from helping with this! Thank you very much!

  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    12,891
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: (VBA) Filter 14 different words, and delete rows that don't contain those words

    You're welcome

  5. #5
    Board Regular
    Join Date
    Sep 2005
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (VBA) Filter 14 different words, and delete rows that don't contain those words

      
    Quote Originally Posted by MickG View Post
    Try :-
    Code:
    Sub MG18Aug19
    Dim Rng As Range, Dn As Range, n As Long
    Dim Lst As Long, Dic As Object
    Application.ScreenUpdating = False
    Lst = Range("B" & Rows.Count).End(xlUp).Row
    Set Dic = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
    Dic.Add "ABC111", ""
    Dic.Add "ABC112", ""
    Dic.Add "ABC123", ""
    Dic.Add "ABC124", ""
    Dic.Add "ABC125", ""
    Dic.Add "ABC126", ""
    Dic.Add "ABC127", ""
    Dic.Add "ABC128", ""
    Dic.Add "ABC129", ""
    Dic.Add "ABC130", ""
    Dic.Add "ABC131", ""
    Dic.Add "ABC132", ""
    Dic.Add "ABC133", ""
    Dic.Add "ABC134", ""
    
    For n = Lst To 1 Step -1
    With Range("B" & n)
        If Not Dic.Exists(.Value) Then .EntireRow.Delete
    End With
    Next
    Application.ScreenUpdating = True
    End Sub
    Regards Mick
    Mick,
    This code works great for specific words but can you modify it to work for cells that contain a variation of a keyword? For example, the cells in column F has something like 10BKT0098 in some of the rows, the macro should look through each cell to see if the keyword "BKT" is in it and keep it but delete every other row in the data set. I have a need for this for 3 different keywords as well. Thanks!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com