deleting rows containing listed numbers in excel

Question

i have a list of numbers in a excel sheet . i.e . one collumn about 20 rows of numbers. lets say its called list A then i have some multi row multi collumn excel sheet2 in which collumn C may contain some of the numbers of list A. how can i delete the rows in excel sheet2 which contain list A numbers in collumn C

Answer 1

It is not really possible to delete rows from Excel through ADO (http://support.microsoft.com/kb/257819), however, you can create a new sheet that does not include the rows you want to delete. You might find this easier with column headings and named ranges. Here is an example that does not include column headings or ranges, so the SQL refers to the various columns as F*n* (default) and the sheets as [Sheet*n*$]

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Set rs = CreateObject("ADODB.Recordset")

strSQL = "SELECT x.F1, x.F2, x.F3, x.F4, x.F5 FROM [Sheet1$] As x " _
       & "LEFT JOIN " _
       & "[Sheet2$] As y " _
       & "ON x.F3=y.F1 " _
       & "WHERE y.F1 Is Null"

rs.Open strSQL, cn

For i = 0 To rs.Fields.Count - 1
    Sheets("Sheet3").Cells(1, i + 1) = rs.Fields(i).Name
Next

Sheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

Answer 2

The non-VBA way to do this is:

• select the numbers in list A
• create a new Custom List of those numbers: Tools>Options then Custom Lists in Excel 2003
• go to sheet2 and sort column C using the new custom list: Data>Sort, Options and then change the First key sort order to your new custom list

All of the list A rows will now be grouped together at the top (or bottom if you sort descending) of your list and you can then delete them