Row must equal one of the following, or be deleted

[samjaynes]

I have an worksheet with over a 2,000 records, which I append to a tabl
through ADO. In order to limit the number of records appended to th
table, I would like to delete the rows that column K does not equal on
of the following (such as apples, bananas, oranges, mangos, etc)

First, I need to know how to write this statement AND if it should be
in a seperate loop than the ADO recordset appending statement.

Thanks..

 

[Jamie Collins]

I have an worksheet with over a 2,000 records, which I append to a table
through ADO. In order to limit the number of records appended to the
table, I would like to delete the rows that column K does not equal one
of the following (such as apples, bananas, oranges, mangos, etc)

First, I need to know how to write this statement AND if it should be a
in a seperate loop than the ADO recordset appending statement.

If this was .Excel.Misc, I'd say use autofilter to show just the
unwanted rows and delete them manually.

Because this is .Excel.Programming I'll say, why delete the unwanted
rows? You could simply disregard them during processing and delete
*all* rows when you are done.

How are you currently adding the rows to the recordset? If you are
looping through your worksheet row by row, this would be a good point
to test for unwanted rows.

For a more detailed answer, post your existing code.

FWIW if your database is another Jet datasource (mdb, xls, csv, txt,
etc) then you should be able to do this in one hit (without the
recordset) using a JOIN e.g. (aircode)

INSERT INTO
[MS Access;Database=C:\MyJetDB.mdb;].MyTable
(MyKeyCol, MyDataCol)
SELECT
F1 AS MyKeyCol, F2 AS MyDataCol
FROM
[Excel 8.0;HDR=No;Database=C:\MyWorkbook.xls;].[Sheet1$J:K]
WHERE
F2 NOT IN ('apples', 'bananas', 'oranges', 'mangos')
;

Jamie.

--