Remove Row In Listbox Based On Criteria

yegarboy · Aug 11th 2014

Greeting,

I have a UserForm with a Listbox that contains up to 100 entries. It is 3 Columns of data wide. I need a loop that will search each row in column 3 for the letter "P" if found remove the row from the Listbox. Thanks for any help. Listboxes are not one of my strong points

Cheers

**iwrk4dedpr** · Aug 12th 2014

Re: Remove Row In Listbox Based On Criteria

How is the list populated?

if it's with a row source you'll need to delete the actual row ( probably not what you wanted )
if it's loaded with .additem then just loop through the entries and remove the item .. you should see a delete method in the listbox methods.

yegarboy · Aug 12th 2014

Re: Remove Row In Listbox Based On Criteria

Its using the add item event. I have a code that removes the selected line if I click on it but there are way to many lines to do this manually. I need a loop lol.

**royUK** · Aug 12th 2014

Re: Remove Row In Listbox Based On Criteria

You have to select the items then use RemoveItem. How elsewould you determine which lines to delete?

yegarboy · Aug 12th 2014

Re: Remove Row In Listbox Based On Criteria

I was told it was possible to loop through the Listbox Objects. If the Object can be selected shouldn't a String be able to be assigned?

Here is an example...

Lets say the very top row of data in the list box is..

Column1 Column2 Column3
0001 Dog P

I would like to Automatically remove that row because Column3 has the letter P in it. I don't want to have to select it manually for each row. The Data within the sheet must remain intact also. Plus the data in Listbox1 is then sent to Listbox2.

I hope this helps clear things up.

Thanks for any insight,.

**cytop** · Aug 12th 2014

Re: Remove Row In Listbox Based On Criteria

When removing items from a Listbox/ComboBox/ListView (or anything where you can iterate through the items) you should work backwards. That way you don't get confused as to which item is be be checked next...

A Multi Column Listbox/ComboBox uses an offset to identify the columns. So to remove items where the 3rd column is a 'P' use something like...

Code

Sub x()


   Dim lngLoop As Long
   
   For lngLoop = ListBox1.ListCount - 1 To 0 Step -1
      '// Offsets are 0 based
      If ListBox1.List(lngLoop, 2) = "P" Then
         ListBox1.RemoveItem (lngLoop)
      End If
   Next
   
End Sub

Display More

yegarboy · Aug 12th 2014

Re: Remove Row In Listbox Based On Criteria

Thanks for the lesson It works perfectly. I knew it could be done, I just had no clue as how to go about it.

Remove Row In Listbox Based On Criteria

Participate now!

Share