For each x in y, delete row if match. Only deletes half of the matches?

**thelisa** · 10-17-2013, 04:07 PM

A user form pops up and is populated with all of the Vendors from the data in alphabetical order.
The user then selects which Vendor(s) they would like to have their own worksheet in the workbook.
The selected Vendor(s) then populate my "Vendor2" worksheet in alphabetical order.
After it pulls the rows of the Vendor(s) into their own tabs and does some formatting, it then searches the "DM2" and "SC2" (also in alphabetical order) tabs for the selected Vendor(s) and deletes them from those sheets.

However, it only deletes half of them. Say there are 4 occurances of the vendor in the "DM2" sheet, it will delete two of them. If you run it again, (now there are 2), it will delete one of them.
If I keep running it, eventually they will be gone, but I do not want to have to run it over and over.

I have used this method before and it has worked fine, however, I do not understand why it is doing this.

Below is my code snippet. Any help would be greatly appreciated.

Please Login or Register  to view this content.

**Solus Rankin** · 10-17-2013, 04:54 PM

Are the cells getting missed consecutive?

I would try looping through them backwards. I think you might be skipping the next row when one is deleted. If you delete row 102 the macro will not check it again even though new information (previously 103) shifted into it.

**thelisa** · 10-17-2013, 04:56 PM

Thank you Solus. Though as i got your reply i was about to put my new solution.

instead of

Please Login or Register  to view this content.

i am using

Please Login or Register  to view this content.

and it works fine and accomplishes what it needs to (they just don't want to see them on the ohter sheets).

I realized that once it is deleted, then the numbers are different. So it makes sense to have it be reversed.

**6StringJazzer** · 10-17-2013, 05:01 PM

I don't have time to set up a file that matches the setup I see in your code but the problem is that in your For loop you are deleting a row, so x doesn't point to a valid cell anymore. Then the For loop goes to the next cell, which ends up being the next cell if you had not deleted the first one. So it skips over a cell every time you do a delete.

A way to avoid this is to use a Do While instead of a For loop, and use a row counter instead of a cell. Then if you delete a row, don't increment the row counter.

Here is an example similar to yours which skips rows that it should delete, and another example that works properly:

Please Login or Register  to view this content.

I have used this method before and it has worked fine

I'll bet you did something different.

**Solus Rankin** · 10-17-2013, 05:10 PM

This is untested with no sample data but you might try this if you don't want to hide the rows.

Please Login or Register  to view this content.

**thelisa** · 10-17-2013, 05:11 PM

Thanks everyone, but I am happy with my own solution and marked the thread as solved once i put it up.

For each x in y, delete row if match. Only deletes half of the matches?

LinkBack

Thread Tools

Rate This Thread

Display

For each x in y, delete row if match. Only deletes half of the matches?

Re: For each x in y, delete row if match. Only deletes half of the matches?

Re: For each x in y, delete row if match. Only deletes half of the matches?

Re: For each x in y, delete row if match. Only deletes half of the matches?

Re: For each x in y, delete row if match. Only deletes half of the matches?

Re: For each x in y, delete row if match. Only deletes half of the matches?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX MATCH array formula that matches substring n gives multiple matches

[SOLVED] Macro to delete rows with zero in column deletes row that contain 0 in any number

How to ignore blank matches with an index and choose the next match (if multiple matches)

CTL - deletes row but moves to cell, need shortcut for more delete row only

My delete row button deletes only the cell not the row. Bug?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions