If Search Delete Next Row?

[MMortal]

I do mailing lists and I have to clean up the list so only one household get a mailing. I use this formula to merge first names if the last name and address are the same.

=IF(AND(D3=D2,C3=C2),B2&" AND "&B3&"", B2)

As you can see this will merge the first name, add "and" then merge the second person's first name. That leaves the second person's first name in the next row. I than have to go delete the extra row of the person not merged. I would like to create a macro that does something like this:

=IF(ISNUMBER(SEARCH("*AND*",B2)),"delete", "Keep")

but delete the next row down.

I have searched this forum and all over Google and found ways to search and delete the row of the cell, but nothing on deleting the next entire row down. I used just pull the data I needed using SQL from the Database but my business changed database companies and they don't want anybody in there.

Thank you in advance.

 

[MMortal]

I figured out a way to do this! I am not using a Macro. I used this formula on the end: =IF(ISNUMBER(SEARCH("* AND *", A1)),"Delete", "Keep") than copied that row pasted special values. Sort, Delete, done. Hope this helps somebody out there with a similar issue.

 

[MMortal]

Important part here is to have everything custom sorted by address than last name run first formula. Than run the solution formula on the end.

 

[James Snyder]

I was going to add that you could use the VBA construct of .Offset(column, row +1) .delete if you end up doing it in VBA.