VBA - Row Deletion using If/Then Statement

[CJLuke]

Ok, the spreadsheet I am working on deals with security identifiers called
CUSIPS. I get a report each month that lists a bunch of security exceptions
by CUSIP that are outside of policy. I have a list of securtity cusips that
are cleared (meaning they are not exceptions), and I have a spreadsheet with
this list in a range. I have tried using the IF function to match the current
report cusips with the cleared list of cusips (to identify which rows of the
report I can delete) but, for some reason, this is not working. Ideally, I
have been trying to generate a macro that will search for the cleared cusips
and automatically delete the row in which it finds the cleared cusip. I have
been trying to use the If, Then, Else statement in VBA, but I am not very
familiar with the object model just yet so writing the program free hand is
causing me a lot of problems.

Instead of trying to give an example of the spreadsheet in this post, it
would be great if I could send it off to someone who might be willing to take
a look at what I am trying to do...Thanks!

Charles

 

[matt]

Ok, the spreadsheet I am working on deals with security identifiers called
CUSIPS. I get a report each month that lists a bunch of security exceptions
by CUSIP that are outside of policy. I have a list of securtity cusips that
are cleared (meaning they are not exceptions), and I have a spreadsheet with
this list in a range. I have tried using the IF function to match the current
report cusips with the cleared list of cusips (to identify which rows of the
report I can delete) but, for some reason, this is not working. Ideally, I
have been trying to generate a macro that will search for the cleared cusips
and automatically delete the row in which it finds the cleared cusip. I have
been trying to use the If, Then, Else statement in VBA, but I am not very
familiar with the object model just yet so writing the program free hand is
causing me a lot of problems.

Instead of trying to give an example of the spreadsheet in this post, it
would be great if I could send it off to someone who might be willing to take
a look at what I am trying to do...Thanks!

Charles

Charles,

It sounds like the WRDS database is giving you cause to do some
research. You could send it to me and I'll take a look at it for
you. Alternatively, you could utilize the VLOOKUP function in the
data set and then have the macro search the VLOOKUP column for the
deletion text and then delete the corresponding row.

Matt

 

[Andy Smith]

You don't need VBA. You've set up two worksheets, the input CUSIPS (which
might have bad ones), and the cleared CUSIPS, so here's what to do:

1. Make sure there are column headings on both sheets, and that the CUSIP
heading is the same on both.

2. Select the whole input range -- the CUSIPS and any additional columns,
with headings -- and do Data/Filter/Advanced.

3. Set the Criteria range to be the one column of cleared CUSIPS, again with
the heading.

4. Choose Filter In Place, click OK, and that will hide the bad ones.

5. To reveal them all again (since the list of cleared ones will change), do
Data / Filter / Show All.

 

[CJLuke]

You're right...I don't need VBA...My knowledge of Excel keeps growing day by
day...those advanced filters are very helpful...I appreciate the responses!!!
Thanks...

Charles