Deleting blocks of rows (same first column entry) according to multiple criteria using VBA

[fxrexcel]

I have the following data and the desired outcome, see below. I have about 100,000 entries. I will a loop with VBA that does the following:

• If a company contains in column 2 either of the following PE, M&A, Debt (these are actually many more, I have a list of what to exclude), then delete all rows of that company
• If a company's column 2 entries do not contain any PE, M&A, Debt, then do not delete this company from the list
• E.g.:
  • Company 1 contains a PE, M&A deal --- hence all 5 rows of Company 1 need to be deleted
  • Company 2 does not contain any of PE, M&A, Debt deals --- hence keep all rows of Company 2

Data:

Name | Deal

Company 1 PE
Company 1 M&A
Company 1 Seed
Company 1 Seed
Company 1 Angel
Company 2 Seed
Company 2 -
Company 2 Seed
Company 3 Debt
Company 4 Angel
Company 4 -
Company 4 Angel
Company 4 Seed
… ….
… ….

Outcome:

Name | Deal

Company 2 Seed
Company 2 -
Company 2 Seed
Company 4 Angel
Company 4 -
Company 4 Angel
Company 4 Seed
… ….
… ….

Thanks a lot for your help!

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...ry-according-multiple-criteria-using-vba.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.