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)
any help would be appreciated!
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!