Deleting certain rows

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
I get a list every week of 500-600 employees starting in row 15. Rows 1-14 are the heading rows. I than copy that tab and remove all except 26 employees from the new tab. I can't record an if statement because I have to many employees. Is there a way I can delete all rows in Rows 15 to Rows 600 that don't have the following employee ID's. I've only listed a few for the example, but I will use the methodology provided for my larger list.

Employee ID's

569856
365874
369857
365894
365874
365987
698743
359874
365987




The employee ID's are in Column A.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
B15=ISNA(MATCH(A15,D:D,0)) where column D has the employee numbers you'd like to keep.

Copy down, filter column B for TRUE, select rows 15:1000 (or whatever the last row is), press ALT ; to select only the visible cells, and press CTRL - to delete the rows which did not match. Remove the filter and you should have only the desired rows left.

Or, use VBA :)
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

How about having this list somewhere e.g. J1:J26

in B15 enter the formula

=IF(ISERROR(MATCH(A1,$J$1:$J$25,0)),"N","Y")

and copy downas far as necessary.

you can the filter on column B for Y then copy this where required.

Do you still need a macro?
 

Forum statistics

Threads
1,136,712
Messages
5,677,335
Members
419,688
Latest member
sarahmichelle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top