Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Exclude Contents From List :

  1. #11
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    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.
    Last edited by snb; 06-07-2013 at 03:49 PM.

  2. #12
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    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.

  3. #13
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    @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.
    Attached Files Attached Files
    Last edited by snb; 06-07-2013 at 08:58 PM.

  4. #14
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    @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).

  5. #15
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    @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.
    Last edited by Rick Rothstein; 06-07-2013 at 09:43 PM.

  6. #16
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    @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 View Post
    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.

  7. #17
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    My medication prevents my inclination

Similar Threads

  1. Replies: 5
    Last Post: 01-12-2013, 02:49 AM
  2. Delete List Contain Matching from Second List
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 12
    Last Post: 10-07-2012, 07:18 PM
  3. Macro for Opening files and copy the contents of the File
    By ravichandavar in forum Excel Help
    Replies: 16
    Last Post: 08-15-2012, 09:17 PM
  4. Macro to create files as per the contents in a column
    By Praveen Bj in forum Excel Help
    Replies: 1
    Last Post: 07-05-2012, 09:07 PM
  5. Replies: 4
    Last Post: 05-14-2012, 11:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •