Search for matching text, then delete rows above it.

**mainer188** · 07-08-2009, 09:46 PM

Here is what Im trying to do: I have a VERY large excel file that has groups of 4 cells, a blank, then another 4, repeating. Kinda like the following:

ExampleCell1
ExampleCell2
ExampleCell3
UID = example

ExampleCell1
ExampleCell2
ExampleCell3

ExampleCell1
ExampleCell2
ExampleCell3
UID = example

That pattern continues for about 3000 lines. However, if you noticed, some of the "groups" dont have the 4th line "UID = example".

Here is what Im trying to do. I want excel to search for the cells with "UID" and DELETE it plus the 3 rows above. So that only the "groups" without the UID remain.

Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 3000+ lines of text.
Any suggestions are GREATLY appreciated!!!!!!!

**shg** · 07-08-2009, 11:11 PM

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

**shg** · 07-09-2009, 03:31 PM

Thank you.

Try this:

Please Login or Register  to view this content.

**Phil_V** · 07-09-2009, 05:12 PM

I'm sure I posted a solution to this earlier, but it seems to have been deleted along with the sample workbook that had been uploaded previously?

**shg** · 07-09-2009, 05:24 PM

Not by me, Phil. Had you posted it after my request for a thread title change, I'd have soft-deleted it and restored it after the title was changed.

**mainer188** · 07-09-2009, 05:27 PM

Originally Posted by Phil_V

I'm sure I posted a solution to this earlier, but it seems to have been deleted along with the sample workbook that had been uploaded previously?

Phil, you did post a reply, but it was for a duplicate thread. Oops
Now if only I can figure out how to delete this old one.....

**Phil_V** · 07-09-2009, 05:30 PM

Well, I didn't keep a copy of my previous code, but this is my 'submission':

Please Login or Register  to view this content.

This will also remove the surplus blank lines that are otherwise left by shg's code.
Also note that my find text is "UID: ", as in the earlier post it was stated that the "=" was a mistake and the data actually had ":" instead.

I wasn't accusing you shg, I hope you understand that, just wondered where the earlier responses had gone.

EDIT I KNEW I'd seen it somewhere before, seems posts weren't deleted, just a very bad case of cross-posting on the same forum

See here:
I need code to search for certain text, then delete the row, plus others. $30 REWARD

**shg** · 07-09-2009, 05:33 PM

Phil,

It's still posted at http://www.excelforum.com/excel-prog...30-reward.html

... but it was for a duplicate thread. Oops

mainer, please don't do that again.

**hansek** · 11-08-2013, 11:10 AM

Originally Posted by shg

Thank you.

Try this:

Please Login or Register  to view this content.

This works wonderfully for my application, but "UID" shows up in more than one column so its deleting too much data. Is there a way to specify that I only want it to fine "UID" in column A and then delete its row and the three rows above?