Search Cell Value Delete if Not in Named Range

[Julia55]

I am pretty new to VBA I am basically trying to search all the cell values in column K of multiple sheets. If the value in column K is not found in my named range I want it to delete the entire row. Any help is greatly appreciated!!!!

Please Login or Register  to view this content.

[stnkynts]

1. What is your named Range called and what sheet is it on?
2. Exactly which sheets do you want to compare column K to the named Range?
3. Do you want to delete the row of column K when it does not match anything in the named range OR do you want to delete the row of the named range if it does not match anything in column K?
4. Are there any other sheets that will have nothing to do with this? (dont want to set a macro to work across all sheets if it needs to be specific)

[Julia55]

1. What is your named Range called and what sheet is it on?
2. Exactly which sheets do you want to compare column K to the named Range?
3. Do you want to delete the row of column K when it does not match anything in the named range OR do you want to delete the row of the named range if it does not match anything in column K?
4. Are there any other sheets that will have nothing to do with this? (dont want to set a macro to work across all sheets if it needs to be specific)

1. The named range is called "TABMODELS"
2. Sheet3 i want to compare column J to the named range, Sheet4 & Sheet5 I want to compare column K to the named range.
3. I want to delete the row of column K/J when it does not match anything in the named range.
4. There are other sheets in the workbook, so it does have to be sheet specific.

[stnkynts]

I assumed that the named range was on a sheet called Sheet1. I also assumed that Sheet4 and Sheet5 are the 4th and 5th sheet numerically. I set the loop to go to the first row so if you have a header you may want to change that or else the header is going to go away. I would suggest trying this on a copy of your workbook since there will be a lot of deleting going on.

Please Login or Register  to view this content.

[Julia55]

Please Login or Register  to view this content.

Is there anyway to make this stop deleting at row 3 on sheet 3, 4, & 5? I have headings in row 1 & 2 on sheet3, sheet4, and sheet5 and don't want to delete those rows. Otherwise it works great!

[stnkynts]

Please Login or Register  to view this content.

[Julia55]

Ah, you are genius! Thanks so much for your help! You just saved me tons of manual effort every month!