|
Delete row 'if'
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 |
What if your Line 3 we
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" wrote in message ups.com... 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,
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 wrote: What if your Line 3 we 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" wrote in message ups.com... 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 |
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" wrote in message ups.com... 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 wrote: What if your Line 3 we 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" wrote in message ups.com... 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,
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 |
| All times are GMT +1. The time now is 05:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com