Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

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
0
Fordraiders
Asked:
Fordraiders
  • 6
  • 3
  • 2
  • +1
2 Solutions
 
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 LissRetired ProgrammerCommented:
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now