Delete row 'if'

[zack]

I would grateful for any assistance, as fairly new to VBA and producing
macros.

On a regular basis I have lists of contacts where I have to manually
delete rows that meet certain criteria. This is a time comsuming
process, that I need to automate.

Each row of data includes fields, eg title,first
name,surname,address,active address,etc.
The data is presorted by 'surname' and then 'firstname'.
One person may appear any number of times.
The 'active address' column is populated with either a 'y' or 'n'.
I need a way to delete any row where an 'n' appears in the
'active_address' column, BUT only if the same person appears on the
line above with a 'y'.
Hope this make sense. Please see example below.
DATE PERSON CODE TITLE FIRST SURNAME ACTIVE ADDRESS
05-Aug-05 193079 MISS Jane Doe Y 12 Smith Street SALFORD M7 4EF
03-Aug-05 193079 MISS Jane Doe Y 12 Smith Street SALFORD M7 4EF
03-Aug-05 193079 MISS Jane Doe N 474 Berry New Road SALFORD M7 4NU
05-Aug-05 193079 MISS Jane Doe N 474 Berry New Road SALFORD M7 4NU
The bottom 2 rows are ones that require deletion.
Kind regards Zack

 

[Jim May]

What if your Line 3 were:
03-Aug-05 193080 MR Jim Doe N 474 Berry New Road SALFORD M7 4NU ?
Because he is unique and has "N" for active address
you wouldn't want him deleted, right?

Use a (new) helper column H and in cell H2 enter:
=IF(AND(COUNTIF(B$2:B2,B2)>1,F2="N"),"Delete","")

and Copy down

Afterwards aply Auto-filter on Column H selecting "Delete"
Delete these rows Only, then Unfilter

Back up your data first,

HTH

 

[zack]

Jim,
Thanks for this but, I would only want to delete rows that contain 'N'
in active_address if there is a corresponding row or rows (eg: same
person)with the active_address set at 'Y'.
Hope this makes it clearer.
Thanks zack

 

[Jim May]

Can you provide instance were my suggested formula
does not furnish you (in ColH) the note "delete", properly?
I'm learning too, here..
Tks,
Jim

 

[zack]

Jim,
This works a treat. Have now added as a VBA module, I just need to
work out how to add on the end how to delete the relevant rows
automatically, rather than using autofilter.
Cheers for you help Zack