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

blbat

Active Member
Joined
Mar 24, 2010
Messages
323
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)
Code:
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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Active Member
Joined
Mar 24, 2010
Messages
323
VBA Code:
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

Active Member
Joined
Mar 24, 2010
Messages
323

ADVERTISEMENT

"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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 24, 2010
Messages
323

ADVERTISEMENT

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

does that require an "END With"?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Oops, yes it does, just after the Next r line.
 

blbat

Active Member
Joined
Mar 24, 2010
Messages
323
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...

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,299
Messages
5,705,573
Members
421,399
Latest member
hjweiss00

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
Top