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!

 

[Fluff]

You need to put a full stop infront of Cells & Rows

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

 

[blbat]

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.

Joe,
Unfortunately I can't post that from here due to local company security settings.

The data in column D is alpha-numeric and looks like this:


DD01
CSE2
CC01
EA01
DD04


and so on....

Does that help?

 

[blbat]

Fluff and Joe4...thanks so much, I know I've asked a good question when I get you both posting in the same thread!

Fluff, I will try your latest first thing in the morning and tell you how it goes.

thanks again to you both!

 

[Joe4]

I think fluff identified your issue in his last post.

 

[blbat]

gents,

I'm taking a stab at it...
so to add a full stop- something like this, (which does NOT work):

With wsA

' Find last row with data in column D:
lr3 = Cells(Rows.Count, "D").End(xlUp).Row

'and a "." to end of cell value:

wsA.Range("$D$2:$D$" & lr3).Value = wsA.Range("$D$2:$D$" & lr3) & "."

End With

I have not attempted this before..I know I need to store the values in column D, or add a helper column...I don't want to have to add a helper column...

 

[Fluff]

Did you actually try the code I suggested?

 

[Joe4]

No, you misunderstand how to use "." when using "With/End With".

See these links for explanations:

Excel VBA Programming - With ... End With

Study another Excel VBA logic structure as you learn about With and End Width.

www.homeandlearn.org

How to Use With… End With Block in VBA Excel?

The with block is used to access all properties and methods of an object without repetition. A With… End With block focuses on the given object and list all the properties and methods of the object or structure.

www.exceltip.com

Try fluff's code and see what happens.

 

[blbat]

Fluff...my apologies, I missed the change.

 

[blbat]

Fluff and Joe, sorry for all my flailing around...I truly appreciate your help in educating me.

I studied your code Fluff...and i just can't figure how a full stop is being inserted, or if you wanted me to figure that out myself? (I failed)

I'm fairly literate with the use of "With and End With" functions...and in this case, a nested "With" statement":

Fluff's Code with my comments added:

With wsA
 ' ''wsA" dimmed as worksheet in Active Workbook
      'Outer With Statement defining the Workbook and Worksheet to be operated on


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

    With CreateObject("scripting.dictionary")     'Fluff's inner "with" defining the Object Created
         .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  'If value not found, delete row and exit inner for loop
                 .Rows(r).Delete
             End If
          Next r
    End With  'ends the inner "with"
End With      'ends the Outer "with"

 

[Fluff]

How about

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")
    Dim Dic As Object
    Set Dic = CreateObject("scripting.dictionary")
    
         Dic.CompareMode = 1
         For i = LBound(arr) To UBound(arr)
            Dic.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 Dic.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