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

Animalised

New Member
Joined
Apr 24, 2016
Messages
31
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:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try :-
Code:
[COLOR="Navy"]Sub[/COLOR] MG18Aug19
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Application.ScreenUpdating = False
Lst = Range("B" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]Set[/COLOR] 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", ""

[COLOR="Navy"]For[/COLOR] n = Lst To 1 [COLOR="Navy"]Step[/COLOR] -1
[COLOR="Navy"]With[/COLOR] Range("B" & n)
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(.Value) [COLOR="Navy"]Then[/COLOR] .EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR]
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Try :-
Code:
[COLOR="Navy"]Sub[/COLOR] MG18Aug19
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Application.ScreenUpdating = False
Lst = Range("B" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]Set[/COLOR] 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", ""

[COLOR="Navy"]For[/COLOR] n = Lst To 1 [COLOR="Navy"]Step[/COLOR] -1
[COLOR="Navy"]With[/COLOR] Range("B" & n)
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(.Value) [COLOR="Navy"]Then[/COLOR] .EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR]
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
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!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top