VBA code: Doesnt delete all the values_Looping thru cells and deleting them

Hello All,

I am using below code to loop through range of values in sheet2, in which first 3 letters of
each value is a code.
I have listed applicable codes in sheet1. With below code, i am trying to delete the values whose codes are
not available in sheet1.
I dont why, this codes doesn't delete all the values.

Suppose if i have 150 values to be deleted(among 1000 values), after running below code, still 50 values remain as it is.
Can any one figure out what the mistake i am doing here..? Why my code doesnt delete all the values..?

Please Login or Register  to view this content.

Instead of using For Each cell, use an index and work up from the bottom. You're probably leap frogging cells as you delete the ones above.

Another point is do not loop sequentially through a range while deleting rows - loop in reverse order and reference the row number in the For ... Next loop.

You have a few range definitions that pull the end cell address from sheets different to the start cell address, which is difficult to follow. If you attach a file it will make it easier for us to analyse. A top of head guess is that it might be that you're deleting rows from the range being searched.

Something like this?

Please Login or Register  to view this content.

Due to confidential reasons, i cannot attach the original file

for info, you're expected to take the time to anonymise a sample of the data...

If you need/want help, it is in your interests to help us help you. A sample file with some typical, though not actual/live, data will make the process much quicker and any solutions offered much more likely to work "out of the box".

MatrixMan would be able to test his solution with your test data, update the sample and return it to you. So, not only would you have the code, but you would be able to see it working in the context of your data. But, your choice.