# Listbox remove dupes not working **URL:** \ **Category:** General\ **Created:** [October 28, 2020, 6:59pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307 "2020-10-28T18:59:58Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 28, 2020, 6:59pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/1 "2020-10-28T18:59:58Z")
Hello! I’ve got a listbox with one column and a list. I want to remove any duplicates. I found a snippet of code on the Forums that seemed to be mostly what I need, and I tweaked and added a line of code to remove the duplicates found, but I’m getting an out of bounds exception error. Any ideas what I have wrong? dim iMaxRow as Integer = (VENDORS\_LISTBOX.ListCount - 2) dim iMaxCol as Integer = (VENDORS\_LISTBOX.ColumnCount - 1) for iRow as Integer = 0 to iMaxRow dim iNextRow as Integer = iRow + 1 for iColumn as Integer = 0 to iMaxCol if VENDORS\_LISTBOX.Cell(iRow, iColumn) \<\> VENDORS\_LISTBOX.Cell(iNextRow, iColumn) then // Not a match, check the next row continue for iRow ``` end ``` next // If we reach this point, the row is a duplicate VENDORS\_LISTBOX.Cell(iNextRow, 1) = “Duplicate” VENDORS\_LISTBOX.RemoveRow(iNextRow) next iRow ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [October 28, 2020, 7:30pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/2 "2020-10-28T19:30:48Z")
You have to work backwards. ```auto for iRow = iMaxRow downto 0 ... ``` (In the future, please use the “code” tag to properly format code you post. It will help us help you.) ---
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 28, 2020, 7:38pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/3 "2020-10-28T19:38:03Z")
I’m looking for it. Where did the Code tag go? ---
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 28, 2020, 7:42pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/4 "2020-10-28T19:42:11Z")
Where does this line go/replace? ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [October 28, 2020, 7:42pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/5 "2020-10-28T19:42:41Z")
Easiest way to do it is to use a single backticks inline, so `this` has backticks around it, or use three backticks on its own line before and after the code. ````auto has ``` before and after ```` ---
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 28, 2020, 7:50pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/6 "2020-10-28T19:50:23Z")
Got it. I swear there used t be a “code” icon in the toolbar. I’m struggling a bit to know where to place the line you gave me in the example I gave, `for iRow = iMaxRow downto 0` ---
**Author:** ![Anthony\_Dellos](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_dellos/32/208_2.png) [@Anthony\_Dellos](https://forum.xojo.com/u/Anthony_Dellos)\ **Post date:** [October 28, 2020, 9:08pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/7 "2020-10-28T21:08:42Z")
Niles, Imagine a list like this: ``` 01 - joe 02 - jane 03 - jane 04 - jim 05 - steve 06 - lisa 07 - lisa 08 - tom 09 - steve 10 - steve ``` If you move _forward_ through the list, you have a problem because your “spot” in the list keeps changing as the list shrinks. When you delete the first Jane, your iRow value is 1. (2-1 because listboxes are zero-based) _On the next iteration of the loop, your iRow is 2_ (next value), _ **but** _… you’re now on Jim (row 4) because you deleted one of the Janes. So you’re iRow value is no longer in sync with the list, it should be 3 (4-1) but it’s 2 instead. By the time you get to the two Steves at the end, your iRow will be up to 6 and when you try too iterate again, you’re going to get an out of bounds error because you’re asking for row 8 (iRow = 7) _ **and no row 8 exists at this point** _. ``` 01 - joe 02 - jane 03 - jim 04 - steve 05 - lisa 06 - tom 07 - steve ``` So instead, start at the end of the list and count down. Since you’re “counting down”, you don’t need to worry about if the list gets shorter. You’re never going to go “out of bounds” because iRow is always _decreasing_. Taking the same list: ``` 01 - joe 02 - jane 03 - jane 04 - jim 05 - steve 06 - lisa 07 - lisa 08 - tom 09 - steve 10 - steve ``` Now when you delete steve at row 10, your next iRow value is 8 (9-1), which is still a valid value, when you delete lisa, your next iRow value is 5 (6-1) which is still fine. You’re never going to try and fetch a row which doesn’t exist anymore, because you’re deleting the _ **last** _ duplicate row in the listbox _ **first** _ in your for…next loop. Even if you were deleting _every_ row in the listbox, there’s still be no OOB exception since the last row you’d delete would be the first row in the listbox. (iRow = 0) So getting back to your code, you’d want to do something like this: ``` dim iLastRow as Integer = (VENDORS_LISTBOX.ListCount - 1) dim iLastColumn as Integer = (VENDORS_LISTBOX.ColumnCount - 1) //for each row, **starting from the last** for iRow as integer = iLastRow DownTo 0 //check every column, they *all* must match row above for the row to be a duplicate //NOT deleting Listbox's columns, so ok to iterate through columns from first to last for iColumn as Integer = 0 to iLastColumn if VENDORS_LISTBOX.Cell(iRow, iColumn) <> VENDORS_LISTBOX.Cell(iNextRow, iColumn) then //if *any* column doesn't match, skip to the next iteration of the loop continue for iRow else //this row is identical to the row "above" it, so remove this row VENDORS_LISTBOX.RemoveRow(iRow)` end if next ``` to remove the duplicate rows. \ HTH, Anthony ---
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 28, 2020, 9:26pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/8 "2020-10-28T21:26:46Z")
Anthony, Thank you for taking the time to lay all this out for me. It does help, and I appreciate it! I am still unable to get the code to work. I had change iNextRow to iLastRow, since you code snippet didn’t define this, and I figure it was something left over and not changed from my original example. Also, it needed an additional next command. Now it compiles, but I get the same outofbounds error as before. Here’s what I’m using: ````auto dim iLastRow as Integer = (VENDORS_LISTBOX.ListCount - 1) dim iLastColumn as Integer = (VENDORS_LISTBOX.ColumnCount - 1) //for each row, **starting from the last** for iRow as integer = iLastRow DownTo 0 //check every column, they *all* must match row above for the row to be a duplicate //NOT deleting Listbox's columns, so ok to iterate through columns from first to last for iColumn as Integer = 0 to iLastColumn if VENDORS_LISTBOX.Cell(iRow, iColumn) <> VENDORS_LISTBOX.Cell(iLastRow, iColumn) then //if *any* column doesn't match, skip to the next iteration of the loop continue for iRow else //this row is identical to the row "above" it, so remove this row VENDORS_LISTBOX.RemoveRow(iRow) end if next next``` ```` ---
**Author:** ![Anthony\_Dellos](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_dellos/32/208_2.png) [@Anthony\_Dellos](https://forum.xojo.com/u/Anthony_Dellos)\ **Post date:** [October 28, 2020, 9:41pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/9 "2020-10-28T21:41:18Z")
You’ve changed what I wrote here: `if VENDORS_LISTBOX.Cell(iRow, iColumn) <> VENDORS_LISTBOX.Cell(iLastRow, iColumn) then` You can’t use iLastRow since it’s just the listcount - 1, it’ll go out of bounds. (it’s set before the loop, so it’s not going to change during loop execution) This is probably closer to what you need. ``` dim iLastRow as Integer = (VENDORS_LISTBOX.ListCount - 1) dim iLastColumn as Integer = (VENDORS_LISTBOX.ColumnCount - 1) //for each row, **starting from the last** for iRow as integer = iLastRow DownTo 1 dim iPreviousRow as integer = iRow - 1 //check every column, they *all* must match row above for the row to be a duplicate //NOT deleting Listbox's columns, so ok to iterate through columns from first to last for iColumn as Integer = 0 to iLastColumn if VENDORS_LISTBOX.Cell(iRow, iColumn) <> VENDORS_LISTBOX.Cell(iPreviousRow, iColumn) then //if *any* column doesn't match, skip to the next iteration of the loop continue for iRow else //this row is identical to the row "above" it, so remove this row VENDORS_LISTBOX.RemoveRow(iRow) end if next next ``` Note, this code _ **doesn’t** _ process the first row; if it did iPreviousRow would still end up OOB (it’d be -1 on the last iteration). ---
**Author:** ![Anthony\_Dellos](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_dellos/32/208_2.png) [@Anthony\_Dellos](https://forum.xojo.com/u/Anthony_Dellos)\ **Post date:** [October 28, 2020, 9:47pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/10 "2020-10-28T21:47:21Z")
One more bug (easy to miss things trying to write forum code). Column count needs to be treated as one-based. ``` dim iLastRow as Integer = (VENDORS_LISTBOX.ListCount - 1) dim iLastColumn as Integer = (VENDORS_LISTBOX.ColumnCount - 1) //for each row, **starting from the last** for iRow as integer = iLastRow DownTo 1 dim iPreviousRow as integer = iRow - 1 //check every column, they *all* must match row above for the row to be a duplicate //NOT deleting Listbox's columns, so ok to iterate through columns from first to last for iColumn as Integer = 0 to iLastColumn -1 // **COLUMN COUNT IS ONE-BASED** if VENDORS_LISTBOX.Cell(iRow, iColumn) <> VENDORS_LISTBOX.Cell(iPreviousRow, iColumn) then //if *any* column doesn't match, skip to the next iteration of the loop continue for iRow else //this row is identical to the row "above" it, so remove this row VENDORS_LISTBOX.RemoveRow(iRow) end if next next ``` ---
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 28, 2020, 10:39pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/11 "2020-10-28T22:39:09Z")
OK, so no more outofbounds errors anymore, but I still get the duplicates in the listbox. ---
**Author:** ![Anthony\_Dellos](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_dellos/32/208_2.png) [@Anthony\_Dellos](https://forum.xojo.com/u/Anthony_Dellos)\ **Post date:** [October 28, 2020, 10:41pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/12 "2020-10-28T22:41:26Z")
I actually tested this code Niles. Here’s a sample project which might help you out. Good luck! > **[deDuplicate.xojo\_binary\_project.zip](https://www.dropbox.com/s/5k2l0y9asa3nvwk/deDuplicate.xojo_binary_project.zip?dl=0)** > > Shared with Dropbox ---
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 28, 2020, 10:52pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/13 "2020-10-28T22:52:21Z")
Looking at your example, I see the problem. My listbox was set to 1 column, not 2. Once I set it to 2 columns (don’t have a 2nd column of info, but oh well.) it works! Thank you very much! ---
**Author:** ![Anthony\_Dellos](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_dellos/32/208_2.png) [@Anthony\_Dellos](https://forum.xojo.com/u/Anthony_Dellos)\ **Post date:** [October 28, 2020, 11:04pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/14 "2020-10-28T23:04:33Z")
Niles. I set it up for multiple columns because _ **your original code** _ appeared to be working with multiple columns. ``` for iColumn as Integer = 0 to iMaxCol if VENDORS_LISTBOX.Cell(iRow, iColumn) <> VENDORS_LISTBOX.Cell(iNextRow, iColumn) then ``` I’d encourage you to actually understand why the code doesn’t work for a single column listbox vs. just adding a second column and moving on. Here’s a hint, you’ll need an if/else condition around this code block: ``` for iColumn as Integer = 0 to iLastColumn -1 if VENDORS_LISTBOX.Cell(iRow, iColumn) <> VENDORS_LISTBOX.Cell(iPreviousRow, iColumn) then //if *any* column doesn't match, skip to the next iteration of the loop continue for iRow else //this row is identical to the row "above" it, so remove this row VENDORS_LISTBOX.RemoveRow(iRow) end if next ``` in order to get the "deDuplicateList " method in the sample project to support _both_ single column and multi-column listboxes. ---
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 28, 2020, 11:48pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/15 "2020-10-28T23:48:50Z")
I mentioned that is was a snippet of code I found, and in the description I mentioned that my listbox was only one column. My apologies if that wasn’t made clear. I tried your single column code out, but it didn’t work on your sample project. The column still had to be set for 2. Honestly, it doesn’t matter. It’s working with 2 columns, and I’m happy. I need a working product at the moment. I’ll make a comment that the column has to be set for 2, if I need to re-use it in the future. ---
**Author:** ![Anthony\_Dellos](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_dellos/32/208_2.png) [@Anthony\_Dellos](https://forum.xojo.com/u/Anthony_Dellos)\ **Post date:** [October 29, 2020, 12:06am UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/16 "2020-10-29T00:06:17Z")
I missed it was a snippet; makes more sense now. I didn’t provide single column code, so I’m not sure what you think you tried? 😉 If the sample code provided worked well enough for you, you should mark it as the solution so the topic shows as answered. For the sake of completeness, this is the code which handles single and multi-column listboxes: ``` dim iLastRow as Integer = (VENDORS_LISTBOX.ListCount - 1) dim iLastColumn as Integer = (VENDORS_LISTBOX.ColumnCount - 1) //for each row, **starting from the last** for iRow as integer = iLastRow DownTo 1 dim iPreviousRow as integer = iRow - 1 //check every column, they *all* must match row above for the row to be a duplicate //NOT deleting Listbox's columns, so ok to iterate through columns from first to last if VENDORS_LISTBOX.ColumnCount > 1 then for iColumn as Integer = 0 to iLastColumn -1 if VENDORS_LISTBOX.Cell(iRow, iColumn) <> VENDORS_LISTBOX.Cell(iPreviousRow, iColumn) then //if *any* column doesn't match, skip to the next iteration of the loop continue for iRow else //this row is identical to the row "above" it, so remove this row VENDORS_LISTBOX.RemoveRow(iRow) end if next else //if just one column, reference it directly if VENDORS_LISTBOX.Cell(iRow, 0) <> VENDORS_LISTBOX.Cell(iPreviousRow, 0) then //if *any* column doesn't match, skip to the next iteration of the loop continue for iRow else //this row is identical to the row "above" it, so remove this row VENDORS_LISTBOX.RemoveRow(iRow) end if end if next ``` ---
**Author:** ![Niles\_Mitchell](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/e495f1/32.png) [@Niles\_Mitchell](https://forum.xojo.com/u/Niles_Mitchell)\ **Post date:** [October 29, 2020, 12:15am UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/17 "2020-10-29T00:15:31Z")
Bingo! Thank you very, very much! ---
**Author:** ![Anthony\_Dellos](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_dellos/32/208_2.png) [@Anthony\_Dellos](https://forum.xojo.com/u/Anthony_Dellos)\ **Post date:** [October 29, 2020, 3:09pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/18 "2020-10-29T15:09:34Z")
You’re welcome. It’s not the prettiest or most efficient code but it does the job. Something you might consider (both for your own edification and improved utility) is to move this method into a Module method which extends the listbox class. (it has to be a Module and the method’s scope needs to be “Public”) Hint, the method signature should include a parameter using the “Extends” keyword, similar to this: `extends lb as listbox` Then for _ **any** _ listbox in the project you could call SomeListbox.deDuplicate. (where “SomeListbox”) is the actual name of a Listbox control. And of course, you could use this module in other projects too… 🙂 ---
**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\ **Post date:** [April 29, 2021, 4:47pm UTC](https://forum.xojo.com/t/listbox-remove-dupes-not-working/58307/19 "2021-04-29T16:47:16Z")