Delete row if "cell.Value" equals a cell in a range

[tonye104292]

Hi all, long time reader, first time poster.

I have the code below (not mine but it runs/works fine) that basically deletes a row in a sheet if a cell in that row equals a specific value (in this case, if it equals Total World, Europe, North, or South).

Im wondering if there is a way to modify the code below so if cell.Value equals a cell in a range in a worksheet, then it deletes that row. This would be instead of me specifying in the code "cell.Value = abc OR cell.Value = def" in order to save time and not specify in the code the hundreds of values that I want to add.

Essentially, I want to post the values in the quotes below "Total World, Europe, North, South" in a range in a worksheet, rather than in the code. Highlighted in red is what I want to modify.

Please Login or Register  to view this content.

[tehneXus]

Hi,

try this file: 2 DelFromList.xlsm

contains:

Please Login or Register  to view this content.

[Jakobshavn]

Here the list of items is in I1 thru I10:

Please Login or Register  to view this content.

[tonye104292]

Thank you both for the quick responses, this seems to have worked. Ill continue testing. One more question, Jakobshavn, if the arraylist is 100000 but there are empty cell values, for example from 7000-100000, will it still check to see if the cell I am checking is equal to the values in those cells (which should be blank)?

EDIT: Jakobshavn, can I make it so the cells it checks against are in a different sheet, in case those rows get deleted? (The column of cells I want it to check against are in a sheet called Keys and column N.)

[Jakobshavn]

Having empty cells in the checklist will not affect the outcome. Make the proper changes to the limits on ary as well as the single line:

Please Login or Register  to view this content.

[rollis13]

Cross-post: http://www.mrexcel.com/forum/excel-q...ell-range.html

[tonye104292]

Having empty cells in the checklist will not affect the outcome. Make the proper changes to the limits on ary as well as the single line:

Please Login or Register  to view this content.

Thank you!