VBA to delete a row if NOT exist...

Hello VBA guru's - - I have a large excel file that column A contains zip codes. All the zip codes are in Illinois to be exact. But I only need to see about 30 zipcodes. I have some code below that will delete the row if the value does not exist. I'd like to create some sort of variable or Dim where I can list the 30 or so zip codes then reference this variable. I'm getting lost on the syntax so a little help would be great!

I'm thinking something like an 'in' in sql? But I'm not 100% sure how that would be done. Is there a way to include another dim that would list each zip, like zipnumber = (60110,60030,60011) instead of repeating the .value as in my example below? Then my if would look more like if .value = zipnumber then delete...? make sense?

Thanks for your thoughts/assistance! Here's my test code (which I got from this great forum!!!!

Please Login or Register  to view this content.

Hello ironman,

This macro uses "Sheet1" and "Sheet2". Sheet1 holds all the zip codes in column "A". Sheet2 column "A" holds the zip codes you want to see. You can change the worksheet names and columns if needed. The macro uses a Dictionary object to test if the desired zip code exists.

Please Login or Register  to view this content.

Hello Ironman,

The Dictionary object was introduced in Office 2000. In the VB Editor go to to Tools > References... Check the refernces for any MISSSING: libraries. Let me know what you find.

Hello ironman,

The references listed are normal. Can you post a copy of the workbook for review?

Hello Ironman,

I made a few changes to the macro. One of which is to save the amount next to the zip code. I figured you will probably need that later on. I have added a button on the Zip worksheet to run the macro. This works on my system which is also a 2003 version. The attached workbook has all the changes made to it.

Please Login or Register  to view this content.

Hello Ironman,

I am glad it is working well. If you need to make changes or have any problems, let me know.