Delete row from database if match to a long list of criteria

[PJ_in_FL]

I'm looking for advise on how to design a procedure to clean out unneeded data from a large database kept in an Excel 2007 spreadsheet. The database is currently 200K items, and what i want to accomplish is to remove any row where one of the cells matches any of about 300 different items.

The exclude list is in a separate sheet beginning in cell A1, and has over 300 string entries in separate rows in the format used for Advanced Filter, e.g. "*this?and?that*" so if any of the database cells in the criterion column contains the words "this", "and", and "that" separated by one character, that would be a match and that row should be deleted.

I've considered writing a macro to successively filter the database for each entry and delete all visible rows, but this process has to be performed each time the database is updated (possibly multiple times each day), so I'm trying to design a time-efficient process from the start.

I started looking through the examples on ExcelForum, OzGrid and VBAExpress I found through Google, though most are only using a single criteria.

Looking for suggestions and other references from the Excel-eratti!

TIA!!!

[Jan Karel Pieterse]

Have a look at this small macro, maybe it gets you started?

Code: Select all

Sub Macro1()
    Dim lct As Long
    Worksheets("Sheet1").Range("A8:A20").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
                                   Worksheets("sheet2").Range("A1:A4"), Unique:=False
'Step backwards through the rows and remove each visible one. Skip top row as it contains the header:
    For lct = Worksheets("Sheet1").Range("A8:A20").Rows.Count To 2 Step -1
        If Worksheets("Sheet1").Range("A8:A20").Cells(lct, 1).EntireRow.Hidden = False Then
            Worksheets("Sheet1").Range("A8:A20").Cells(lct, 1).EntireRow.Delete
        End If
    Next
End Sub

[PJ_in_FL]

Jan Karel,

Thanks for the starter! It helped me remember multiple rows can be used as "OR"'ed criteria. Now the question is, how many can be used at one time in Advanced Filter?

Guess I'll do a binary test (all, half, quarter, etc.) to arrive at the number then report back, unless someone has the information at hand.

Thanks again!

[HansV]

The number of conditions can be quite large - I haven't tried it with 300 conditions, but Excel (2010) didn't bat an eyelash at 175 conditions.