Loop through Column, looking for an array of values, If NOT found, delete entire row

[blbat]

So I tried to modify some good code from Joe4, but ran into some walls.

I'm trying to modify his working loop to cycle through Column "D", find the values in the array, and delete any rows that DON'T have those values.
(With the addition of a "wildcard" at the end of each value in the array)

Sub DeleteAllExcept()

    Dim lr As Long
    Dim arr
    Dim r As Long
    Dim i As Long
    Dim x As String
   
    Application.ScreenUpdating = False

'   Store all values you want to search for in an array
    arr = Array("CSE*", "CC0*", "OE0*", "JOB*")   'I added The "*" wildcard. which I know does not work in this line...
   
'   Find last row with data in column D
    lr = Cells(Rows.Count, "D").End(xlUp).Row
   
'   Loop through all rows from bottom to top
    For r = lr To 1 Step -1
'       Loop through each value in array and check for a match
        For i = LBound(arr) To UBound(arr)
'           Get value to look for
            x = arr(i)
'           Check for value
            If Left(Cells(r, "D"), Len(x)) = x Then  ' I need this line to be if NOT EQUAL to
'               If value found, delete row and exit inner for loop
                Rows(r).Delete
                Exit For
            End If
        Next i
    Next r

    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"

End Sub

any help would be appreciated!

 

[Joe4]

Array("CSE*", "CC0*", "OE0*", "JOB*")

Are the values you are looking for always at the beginning of the string?
If so, remove the wildcard. The code is already not looking for perfect matches, just values that start with those prefixes listed in the array.

 

[Fluff]

How about

Sub blbat()

    Dim lr As Long
    Dim arr
    Dim r As Long
    Dim i As Long
    Dim x As String
   
    Application.ScreenUpdating = False

'   Store all values you want to search for in an array
    arr = Array("CSE", "CC0", "OE0", "JOB")   'I added The "*" wildcard. which I know does not work in this line...
    With CreateObject("scripting.dictionary")
         .CompareMode = 1
         For i = LBound(arr) To UBound(arr)
            .Add arr(i), Nothing
         Next i
'         Find last row with data in column D
         lr = Cells(Rows.Count, "D").End(xlUp).Row
   
'         Loop through all rows from bottom to top
          For r = lr To 1 Step -1
'            Check for value
             If Not .Exists(Left(Cells(r, "D"), 3)) Then  ' I need this line to be if NOT EQUAL to
'                If value not found, delete row and exit inner for loop
                 Rows(r).Delete
             End If
          Next r

    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"

End Sub

 

[blbat]

Array("CSE*", "CC0*", "OE0*", "JOB*")

Are the values you are looking for always at the beginning of the string?
If so, remove the wildcard. The code is already not looking for perfect matches, just values that start with those prefixes listed in the array.

Joe4 Thanks for looking at this. Yes the values are always at the beginning of the string...my problem is that the value after the first three Characters can be different.

 

[blbat]

"If Not .Exists" !!!

yikes I never even considered using this...sheesh. I kept thinking about how can I get a "NOT EQUAL TO" working....

 

[Joe4]

Yes the values are always at the beginning of the string...my problem is that the value after the first three Characters can be different.

It doesn't matter. It is only looking at the beginning characters.
If you are looking for "CSE", it is only looking at the first 3 characters (note the use of the LEFT and LEN functions).

"If Not .Exists" !!!

yikes I never even considered using this...sheesh. I kept thinking about how can I get a "NOT EQUAL TO" working....

OK, I was focusing on the wildcard issue, and hadn't even looked at the other issue yet, but looks like fluff gave you a solution to that.

 

[blbat]

Fluff, the line: With CreateObject("scripting.dictionary")

does that require an "END With"?

 

[Fluff]

Oops, yes it does, just after the Next r line.

 

[blbat]

Gents, here's what I'm using, but it is not functioning. It compiles, but does not perform as expected.
I inserted "TEST" in Column D and it was not removed, looks like nothing else was either...

With wsA            
 ' ''wsA" dimmed as worksheet in Active Workbook

'   Store all values you want to search for in an array
    arr = Array("CSE", "CC0", "OE0")

    With CreateObject("scripting.dictionary")
         .CompareMode = 1
         For i = LBound(arr) To UBound(arr)
            .Add arr(i), Nothing
         Next i
'         Find last row with data in column D
         lr3 = Cells(Rows.Count, "D").End(xlUp).Row
  
'         Loop through all rows from bottom to top
          For r = lr3 To 1 Step -1
'            Check for value
             If Not .Exists(Left(Cells(r, "D"), 3)) Then  ' I need this line to be if NOT EQUAL to
'                If value not found, delete row and exit inner for loop
                 Rows(r).Delete
             End If
          Next r
    End With
End With

 

[Joe4]

Please post a sample of your data so we can see what it looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.