removing items from listbox with OR statement not working

userform
listbox
command button

I have a list of items in a listbox that i wan to filter.

With UserForm2.ListBox32
    For ri = .ListCount - 1 To 0 Step -1
    If .List(ri, 3) <> "DG" Or .List(ri, 3) <> "WD" Or .List(ri, 3) <> "DV" Or .List(ri, 3) <> "WV" Or .List(ri, 3) <> "WG" Then
            .RemoveItem ri
        End If
   Next
End With


Example: data
Column1                              Column2                           Column3
21212                                   apples                                           ST
21213                                   BREAD                                          DG
21299                                   PEANUTS                                       WG
21207                                   ORANAGE                                     ST
21215                                   PIZZA                                            ST
21201                                   TUNA                                           WV
21217                                   SUSHI                                         ST
21200                                   PAYDAY                                       DV
     
After I execute this code all that should be left is
Column1                              Column2                           Column3
21213                                   BREAD                                          DG
21299                                   PEANUTS                                       WG
21201                                   TUNA                                           WV
21200                                   PAYDAY                                       DV



BUT ALL THE DATA GETS deleted..??

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tel2Commented:
Hi fordraiders,

Change your "Or"s to "And"s, like this:

    If .List(ri, 3) <> "DG" And .List(ri, 3) <> "WD" And .List(ri, 3) <> "DV" And .List(ri, 3) <> "WV" And .List(ri, 3) <> "WG" Then

Why?  Because each item must be not equal to most or all of those strings, so the condition will be met, and the data deleted.

But there will be a more concise/elegant way to code that.  One way is to use a regular expression.
0
Martin LissOlder than dirtCommented:
Another way which may easier to understand then regular expressions is this

For ri = .ListCount - 1 To 0 Step -1
    Select Case .List(ri, 3)
        Case "DG", "WD", "DV", "WV", "WG"
            ' These are OK so skip them
        Case Else
            .RemoveItem ri
        End If
    End Select
Next

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tel2Commented:
Or this:

removable = "|DG|WD|DV|WV|WG|"
With UserForm2.ListBox32
    For ri = .ListCount - 1 To 0 Step -1
        If InStr(removable, "|" & .List(ri, 3) & "|") > 0 Then
            .RemoveItem ri
        End If
   Next
End With

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

IrogSintaCommented:
Here's one more way:
For ri = .ListCount - 1 To 0 Step -1
     If InStr("DG, WD, DV, WV, WG",  List(ri, 3) ) = 0 Then
             .RemoveItem ri
     End If
Next

Open in new window

Ron

Edit... Looks like tel2 and I posted about the same time with similar solutions.
0
tel2Commented:
Hi Ron,

Having looked at your code, I now realise that I didn't need the "removable" variable.  I could have just put the list inside the InStr function, as you did.

However, it looks as if your code will remove the item only if it is not found in the list, right?  Did you mean:
    If InStr("DG, WD, DV, WV, WG",  .List(ri, 3) ) > 0 Then
?
I've also changed your 'List' to '.List', in the line above.

Also, if .List(ri, 3) = "G" or " " or "," or ", ", for example, then your code would also remove the item.
Even my code would remove the item, if .List(ri, 3) = "WD|DV", for example.
I don't know how likely any of the above possibilities are, though.
Martin's code should work with any data.
0
IrogSintaCommented:
Tel2,
Thanks for pointing out the missing period in my code.  I also didn't notice that you were using =0 rather than >0.  I believe the OP wants to leave the ones listed in your removable variable so you need to use =0 in your code.

You're right about how my code would react to "G"; however, I was assuming that those were the only possible 2 character strings that can be used in the 3rd column.  The way you did it will work in any scenario.

Ron
0
tel2Commented:
Hi Ron,

> I believe the OP wants to leave the ones listed in your removable variable so you need to use =0 in your code.
Good point - thanks.  I misunderstood that.  Here's my updated code, and I've also removed my "removable" variable (sounds apt, eh?), which makes my code a bit more like yours.
With UserForm2.ListBox32
    For ri = .ListCount - 1 To 0 Step -1
        If InStr("|DG|WD|DV|WV|WG|", "|" & .List(ri, 3) & "|") = 0 Then
            .RemoveItem ri
        End If
    Next
End With

Open in new window

> I was assuming that those were the only possible 2 character strings that can be used in the 3rd column.
A reasonable assumption, which is very likely correct.

> The way you did it will work in any scenario.
I don't think so.  As I said in my last post:
> Even my code would [retain] the item, if .List(ri, 3) = "WD|DV", for example.
Right?
But the '|' separator could be changed to be anything that will never appear in column 3, and that would solve the problem.

The main advantage of our styles over Martin's is, we don't have an (explicit) loop, so it's probably slightly faster and a bit (or even a byte) more concise.  His is probably more readable, though.

tel2
0
FordraidersAuthor Commented:
Thanks very much worked great. Right off the bat
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
tel2Commented:
Hi fordraiders,

I'm a bit annoyed that my answers got 0 points for this.

I was the only person who pointed out the exact (key) cause of your problem (i.e. "Or"s instead of "And"s, which I think is the main issue here), and gave you fixed code, and did that in the first response to your question.  I also gave a concise alternative method, which would most likely work in your situation.  If you prefer Martin's solution, that's fine, but to give me no credit (points) for anything, including my first post, doesn't seem right to me.  It makes me ask myself, why did I bother, and will I bother again in future?  (Points is only part of the reason I answer questions, but I do need some to keep qualified.)

I have no problem with you giving some points to others, but don't see why they should get them all considering the above.

Do you know how to split points over multiple answers, fordraiders?

If you see my point and want to redistribute points, you can click the "Request Attention" link at the bottom-right of the original post, and ask a moderator to re-open it, so you can redistribute them.

Thanks.
tel2
0
Martin LissOlder than dirtCommented:
I don't mide sharing the points so if you Request attention to this thread I have no objection.
0
FordraidersAuthor Commented:
Hope this helps.
0
tel2Commented:
Thanks for redistributing points, fordraiders.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.