Macro to remove rows that contain numbers listed in another worksheet.

[purger84]

Hey Guys. Excel is my weakest application in the MS Office suite and I've looked all over and haven't found anything on how to do this. I have a spreadsheet with 33,000 rows of uniformly formatted data.
I can use the MID function to pull the number I need from each row.
I need to know how to compare a number from each of these rows to the list on a separate worksheet.
If the number does not match any of the numbers from the range on the second worksheet then the row should be deleted.

Does anyone know how to do this? I've found how to compare data to one thing but not a range.

[Mayda89]

There are several ways to do it.
One is:
-On the spreadsheet with the data, input to the right-most column this formula:

Please Login or Register  to view this content.

Note that you should substitute LookupCell with the appropriate cell which will be looked up, the LookupRange with the appropriate lookup range.
(don't forget to put fix the lookup range by dollar signs to make it absolute reference)
Now, copy this formula all the way down.

Finally, sort this table of data (including the newly added column) according to the newly added column in descending order; this way, all the error values will be on top. Now it will be very easy for you to select the whole range which have error values in the newly added column.

I hope I was clear enough

Please don't hesitate, if you have any further questions!


P.S: As you may have realized, in this method you don't need to deal with macros

[purger84]

Thanks Mayda I'll give it a try.

[purger84]

I think I got it.

Please Login or Register  to view this content.

Now, I'm gonna have to re-purpose a macro. Would you be able to help with that too? I've worked with VBS before but I'm not too good at bridging the gap between excel and VBS

[Mayda89]

Glad that you got it to work out!

About the other issue, I guess forum rules dictate that you should go ahead and start a new thread about it. You can send me the link via pm, I'll try to help

[purger84]

This is just part of the function though not an actual Macro.