Exclude Contents From List :

Now using the UI facilities:

Autofilter:

Code:

---

Sub M_snb_003()
    sn = Range("B1:B3")

    With Columns(1)
        .AutoFilter 1, sn(1, 1), xlOr, sn(2, 1)
        .SpecialCells(12).Delete xlShiftUp
        .AutoFilter
        .AutoFilter 1, sn(3, 1)
        .SpecialCells(12).Delete xlShiftUp
        .AutoFilter
    End With
End Sub

---

and advancedfilter

Code:

---

Sub M_snb_004()
    Cells(1, 4).Resize(, 3) = Cells(1, 1).Value
    Cells(2, 4).Resize(, 3) = Array("<>" & Cells(1, 2), "<>" & Cells(2, 2), "<>" & Cells(3, 2))
    Columns(1).AdvancedFilter 2, Cells(1, 4).CurrentRegion, Cells(1, 12)
End Sub

---

The criterion range can be filled manually (or only once); after that you can test the speed of avancedfilter.
For simplicity's sake I added the code to fill the criterion range.

Quote:

---

Originally Posted by snb

Now using the UI facilities:

Autofilter:

Code:

---

Sub M_snb_003()
    sn = Range("B1:B3")

    With Columns(1)
        .AutoFilter 1, sn(1, 1), xlOr, sn(2, 1)
        .SpecialCells(12).Delete xlShiftUp
        .AutoFilter
        .AutoFilter 1, sn(3, 1)
        .SpecialCells(12).Delete xlShiftUp
        .AutoFilter
    End With
End Sub

---

and advancedfilter

Code:

---

Sub M_snb_004()
    Cells(1, 4).Resize(, 3) = Cells(1, 1).Value
    Cells(2, 4).Resize(, 3) = Array("<>" & Cells(1, 2), "<>" & Cells(2, 2), "<>" & Cells(3, 2))
    Columns(1).AdvancedFilter 2, Cells(1, 4).CurrentRegion, Cells(1, 12)
End Sub

---

The criterion range can be filled manually (or only once); after that you can test the speed of avancedfilter.
For simplicity's sake I added the code to fill the criterion range.

---

M_snb_003 came in at 1.15 seconds on the first test, so I did not run any more test for it. As for M_snb_004, I am not sure what should be happening, but nothing seems to happen at all. Well, that is not entirely true, the heading for the list of 10001 continent names (List 1 on my worksheet) gets repeated three times in cells D1:F1 and the cell under them show a not equal sign with one of the names from the "delete me" list, but that is all. The list of 10001 continent names does not change and no other text appears on the worksheet. It takes the code 0.03 seconds (consistently) to do only that.

@Rick

snb_004 puts the items to be removed in a criterion range D1:F2
It's necessary that column A has a column label/ heading in A1 which doesn't appear in the list A2:A10000
The advancedfilter will write the result into column M.

Quote:

---

Originally Posted by snb

@Rick

snb_004 puts the items to be removed in a criterion range D1:F2
It's necessary that column A has a column label/ heading in A1 which doesn't appear in the list A2:A10000
The advancedfilter will write the result into column M.

---

Okay, I see what I was doing wrong. Apparently Advanced Filter is the way to go... not sure where my prior 0.03 seconds came from, but now I get a consistent 0.01 seconds for M_snb_004. Excellent time, although if you put in the necesary "clean up" that you did not do (remove the Defined Names you added to the workbook and delete the text you put in cells D1:F2), it might push the time to 0.02 seconds (I did not test for that because even at 0.02 seconds, this is a worthwhile way to code the procedure).

@Rick

I didn't add any named ranges to the workbook. The VBA code produces those automatically.
I don't think it's of any use to clean those ranges, they will be overwritten automatically by the next advanced filter operation.

Quote:

---

Originally Posted by snb

@Rick

I didn't add any named ranges to the workbook. The VBA code produces those automatically.

---

I wasn't aware that Advanced Filter did that... good to know. Thanks... obviously I don't use Advanced Filter very offer (like hardly at all), but given the speeds I am seeing for it, I think maybe I will from now on.

Quote:

---

Originally Posted by snb

I don't think it's of any use to clean those ranges, they will be overwritten automatically by the next advanced filter operation.

---

If you had any tendencies toward being obsessive-compulsive, you would.:biggrin:

My medication prevents my inclination ;)