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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here's one more way:
Edit... Looks like tel2 and I posted about the same time with similar solutions.
For ri = .ListCount - 1 To 0 Step -1
If InStr("DG, WD, DV, WV, WG", List(ri, 3) ) = 0 Then
.RemoveItem ri
End If
NextEdit... Looks like tel2 and I posted about the same time with similar solutions.
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.
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.
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
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
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.
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
> 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 WithA 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
ASKER
Thanks very much worked great. Right off the bat
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
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
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
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
I don't mide sharing the points so if you Request attention to this thread I have no objection.
ASKER
Hope this helps.
Thanks for redistributing points, 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.