We help IT Professionals succeed at work.

removing items from listbox  with OR statement not working

Fordraiders
Fordraiders asked
on
125 Views
Last Modified: 2014-08-19
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Thanks very much worked great. Right off the bat
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
CERTIFIED EXPERT

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't mide sharing the points so if you Request attention to this thread I have no objection.

Author

Commented:
Hope this helps.
CERTIFIED EXPERT

Commented:
Thanks for redistributing points, fordraiders.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.