macro - filter out every opportunity who doesn't have a placement

[AtseA]

Hello everyone,

Im new here and I have a problem with writing a macro. I hope someone could help me out. First of all, Im not familiair with VBA, but I want to write a macro for my database (see my example). It is necessary because my whole database consists of 20000 rows.

Example:

A1 B1 C1 D1 E1
Pers. Ref Age Gend Opportunity Placement
988696 M 122225 not placed
1247151 39 M 122225 not placed
1281161 39 F 122225 not placed
1314597 46 F 122225 not placed
1336469 40 F 122225 not placed
1349426 63 M 122225 not placed
1370931 46 M 122225 not placed
1374464 33 F 122225 not placed
1374918 48 F 122225 not placed
1377396 33 M 122225 not placed
1377825 37 M 122225 not placed
1380023 24 F 122225 not placed
1380446 34 M 122225 not placed
1381885 28 M 122225 not placed
1392168 30 F 122225 not placed
1395210 43 F 122225 not placed
1359169 60 M 122226 not placed
1384629 49 M 122226 not placed
1378215 54 M 122292 not placed
1279885 40 M 122296 not placed
1320683 49 M 122296 not placed
1351864 41 M 122296 placed
1359677 31 M 122357 not placed
1373525 38 M 122389 not placed
1378170 27 F 122389 not placed

I want to filter out every opportunity who doesn't have a placement. So in my example, opportunity 12296 is valid. It has at least the code: placed. That 12296 has the code not placed as well is necessary. On the other hand code 122225 must be deleted, because it doesn't has a placement. Hopefully my explanation is clear and is someone willing to help me to write a macro for my problem.

Thnx!

[MatrixMan]

Hi -

I'm assuming that you actually want to delete the records rather than just filter them (otherwise, use an autofilter, select Custom from dropdown and set the property to where not equals "not placed") ... but remember that if this is not what you want, then don't save the workbook, because VBA deletions can't be undone. A simpler/non VBA way to do this would be to just sort by the placement row, select all the "not placed" rows and delete ... but here you go:

Please Login or Register  to view this content.

If the column you want is not actually "E" then just change it in the parameter setting. Hope that helps.

MM.

[AtseA]

First of all, thank you MM for helping me.

I tested the macro, but it isn't the one Im looking for. The point is, I don't want to delete all the 'not placed' rows. I only want to delete those rows if a group of the same opportunity (like the group 122225) doesn't have a 'placed'. So one of the group need a placement, otherwise delete.

If a group has a placement (like opportunity group 122296), nothing has to be deleted (not even the 'not placed').

Hopefully my explanation is more clear now.

[MatrixMan]

Ah - I see .. sorry about that ... Try this:

Please Login or Register  to view this content.

Cheers, MM.

[AtseA]

Hmm, to bad, it still doesn't work proper... and I don't no were it goes wrong..

[MatrixMan]

Attach your workbook & I'll take another look ..

[MatrixMan]

Hi - I've changed the code so that your list of opportunities has to be sorted (the code does the sort for you) and makes it run more efficiently. I've tested it on your sample data so see how you go on a full set:

Please Login or Register  to view this content.

Let me know if you still have problems .. MM.

[AtseA]

Here it is. It is a sample of my bigger database. Besides, Im using Excel 2004 for Macosx

[MatrixMan]

Hi - have you run the code below in your workbook? I've just retested it and it works fine. I've added a line to highlight the "placed" opportunities to make it more obvious as well:

Please Login or Register  to view this content.