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

blbat

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
You need to put a full stop infront of Cells & Rows
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
 
Solution

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

blbat

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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,727
Office Version
  1. 365
Platform
  1. Windows
I think fluff identified your issue in his last post.
 

blbat

Active Member
Joined
Mar 24, 2010
Messages
318

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
Did you actually try the code I suggested?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,727
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

See these links for explanations:

Try fluff's code and see what happens.
 

blbat

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
How about
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")
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,619
Messages
5,659,917
Members
418,536
Latest member
Tezzies

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