Delete Rows if Name is not in the cell

[saeed9313]

Hi folks,

I have tried different macros but somehow it is not working for me although I am trying to do a simple thing, I think. Here is the situation; I am looking for macro to be called "NAMES". My workbook has five sheets named David, Sarah, Rob, Smith and Bob. Each sheet has lot of data (over 5000 rows) but column H in each sheet has those names. I want to delete all those rows in a sheet which doesn't match with that sheet name. For example, in sheet Sarah, I want to delete all those rows where name is other than Sarah in column H. I want to do that in all for five sheet. So finally each sheet has data only for the person after which sheet has been name. I can do it by sorting but I have to repeat it many times so I need a macro. I would appreciate any help. Thanks!

[Artik_PL]

1. Filter column H,. In Sarah sheet as a criterion enter "<> Sarah".
2. Select the visible rows and delete them.
Ready

See also this thread.

Artik

[saeed9313]

Thanks Artik, as I mentioned I can easily do it by sorting but I need a macro as there will be more names and sheets. Thanks!

[Artik_PL]

Try this code

Please Login or Register  to view this content.

Artik

[saeed9313]

Hi Artik,

This worked great. Thanks for your help. Only problem is i have some other sheets also which contain other related data but since this procedure is going to every wks, so it is deleting data in those sheets too. I was thinking to limit macro to one sheet where I put "strName" (e.g. Sarah) instead of coming from wks name. I can copy procedure after putting "Sheets("XXX").Select" statement and changing strName in it (e.g. Smith) and so on. I tried but it is not working for me. I would appreciate if you can change it like that. Thanks in advance.

[Artik_PL]

It's a small modification:

Please Login or Register  to view this content.

Artik

[saeed9313]

Hi, it is still processing other sheets. I only replaced my sheets name under "NotprocessSheets. Here is my code. Is it possible to specift\y those sheets which needs to be processed on the macro and we don't need to worry about other sheets (which could be added or deleted later). Here are the five sheets I want to process (and will add more to the macro if needed); Bob, Sarah, Smith, Rob, Joe. Thanks!

Please Login or Register  to view this content.

[Artik_PL]

Please Login or Register  to view this content.

Artik

[saeed9313]

Thanks Atrrik, I really appreciate your help. Take care!