OzGrid

How to delete rows if cell doesn't contain criteria

Requirement:

The user has a sheet with thousands of rows and wants to delete the entire row if Column D doesn't contain a certain criteria.

If D2 <> contain "XXA", "XXB", "XXC", "XXD", "XXE", OR "XXJ" then the user wants to delete that row and check the next row.

There are numbers/letters before and after the criteria so the user is not sure if to  put a * before and after (ie "*XXA*").

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149901-deleting-rows-if-cell-doesn-t-contain-criteria

Solution:

Sub Button1_Click()
    Dim lSTrW As Long
    Dim c As Range, x
    lSTrW = Cells(Rows.Count, "D").End(xlUp).Row
    For x = lSTrW To 2 Step -1
        Set c = Cells(x, "D")
        If Not c Like "*XXA*" And Not c Like "*XXB*" And Not c Like "*XXC*" And Not c Like "*XXE*" And Not c Like "*XXJ*" Then
            Cells(x, "D").EntireRow.Delete
        End If
    Next x
End Sub

or possible shortened to

If Not c Like "*XX[ABCEJ]*" Then

or

If  c Like "*XX[!ABCEJ]*" Then

https://msdn.microsoft.com/VBA/Langu.../like-operator

Obtained from the OzGrid Help Forum.

Solution provided by davesexcel and pike.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery