Find certain text and delete row then show MsgBox

[D_N_L]

Hi all,

I've almost got this but not quite so I've resorted to posting in this excellent forum.

I'm trying to have a macro search columns A:B (to last row with data) and then delete any rows which contain the sequence of letters "test1" OR "test2".

I say sequence of letters because there could be other string in the cell and the word "test1" may have characters before or after it, i.e. "ttest1" "test12". In both these examples the rows would be deleted. Also I don't want it to be case sensitive so "teST1",TEst1" would be deleted too.

Then a MsgBox to say how many instances of Test1 and Test2 were deleted.

Thanks

[nilem]

Hi Plumby,
maybe something like this

Please Login or Register  to view this content.

[D_N_L]

Hi Nilem,

Sorry, that did not work. I received the 'Cannot use that command on overlapping sections' error for the last line of code

Please Login or Register  to view this content.

[nilem]

Can you attach an example of your file

[D_N_L]

Sure. Here you go I have attached dummy data. In this example all the rows would be deleted.

[nilem]

try again

Please Login or Register  to view this content.

[D_N_L]

Brilliant!! Thank you!

How would I add a MsgBox to this at the end to state how many instances of Test1 and Test2 were found?

[D_N_L]

Hi Nilem,

I've sorted the MsgBox out. Thank you for your help I shall mark this as SOLVED. Can I ask what this line of code does please:

Please Login or Register  to view this content.

[nilem]

Please Login or Register  to view this content.

SpecialCells(2, 4) means SpecialCells(xlCellTypeConstants, xlLogical)

[D_N_L]

Just seen your code. This is my MsgBox code:

Please Login or Register  to view this content.