Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Exclude Contents From List :

  1. #1
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    13

    Lightbulb Exclude Contents From List :

    Rajan Verma has posted the following on 10-04-2012 10:14 PM:

    Hi, What would be your approach to do this job * Suppose you have 2 List As showing below , and you need to remove List2 contents from List 1 , here is better code to accomplish this job , By using dictionary i just tried to make this process fast Sub ExcludeFromList() * *** […]

    Exclude Contents From List :

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    I created a list of the seven continents spread down 10000 rows and then created a list of three of them to be removed. My tests showed the shorter code below (containing about half as many activce lines of code as your routine) executes quicker that the dictionary method you posted. I would note, though, that we are talking about the difference between super-quick and even more super-quick... the user would never be able to tell the difference in speeds of our codes by simple observation. I would also note that I used the same Defined Names ranges as you did for identifying the beginning of the lists.
    Code:
    Sub ExcludeFromList()
      Dim X As Long, Index As Long, sRemove As String, vOut As Variant, vList As Variant
      Index = 1
      vList = Range("rngRange").CurrentRegion
      ReDim vOut(1 To UBound(vList), 1 To 1)
      sRemove = Chr(1) & Mid(Join(Application.Transpose(Range("MapDelete"). _
                CurrentRegion.Value), Chr(1)), Len(Range("MapDelete")) + 1) & Chr(1)
      For X = 2 To UBound(vList)
        If InStr(sRemove, Chr(1) & vList(X, 1) & Chr(1)) = 0 Then
          Index = Index + 1
          vOut(Index, 1) = vList(X, 1)
        End If
      Next
      Range("rngRange").CurrentRegion = vOut
    End Sub
    Note: I assumed the same Defined Names ranges as you did.

  3. #3
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    What about:

    Code:
    Sub M_snb()
        sn = Range("B1:B3")
        For Each it In sn
            Columns(1).Replace it, "", 1
        Next
    End Sub
    Code:
    Sub M_snb()
        sn = Range("B1:B3")
        For Each it In sn
            Columns(1).Replace it, "", 1
        Next
        columns(1).specialcells(4).entirerow.delete
    End Sub
    Last edited by snb; 06-07-2013 at 12:32 AM.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    What about:

    Code:
    Sub M_snb()
        sn = Range("B1:B3")
        For Each it In sn
            Columns(1).Replace it, "", 1
        Next
    End Sub
    On my computer, my code executes in 0.02 seconds on average where as Rajan's executes in 0.03 seconds on average... the code you posted executes in 0.03 seconds on average; HOWEVER the list your code produces is not the same as the list Rajan's and my code produce. The list your code produces is littered with blank cells whereas our list have no blank cells in them. If I add this line of code to your code...

    Columns(1).SpecialCells(xlBlanks).Delete xlShiftUp

    in order to "squeeze out" the blank cells, the code's time balloons to just under 2.5 seconds.

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    I posted an amendment earlier.

    Another approach:
    Code:
    Sub M_snb()
        [A1:A1000] = [if(A1:A1000=B1,"",if(A1:A1000=B2,"",if(A1:A1000=B3,"",A1:A1000)))]
        Columns(1).SpecialCells(4).Delete
    End Sub

    EDIT NOTE FROM RICK ROTHSTEIN
    -------------------------------------
    Sorry, but I accidentally edited (instead of replying) to your message. I think I put it back the way you had it, but if not, I apologize.
    Last edited by Rick Rothstein; 06-07-2013 at 01:32 AM.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    I posted an amendment earlier.

    Another approach:
    Code:
    Sub M_snb()
        [A1:A1000] = [if(A1:A1000=B1,"",if(A1:A1000=B2,"",if(A1:A1000=B3,"",A1:A1000)))]
        Columns(1).SpecialCells(4).Delete
    End Sub
    I corrected your last row number to 10002 to match the setup I used for the other tests... your code above executes in 0.36 seconds on average. An interesting side note... I originally had a backup copy of my lists in Columns J and K (so I could perform the tests against the exact same lists each time) and, because of that, your Delete pulled the data sideways from the left rather than upwards from the bottom. I moved my lists to another sheet before testing your code for this response... just thought I would alert the readers of this thread about the possible problems that can result by relying on default values/settings.

  7. #7
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    or:
    Code:
    Sub M_snb()
        sn = Range("B1:B3")
        sp = Filter(Filter(Filter(Application.Transpose(Range("A1:A1000")), sn(1, 1), False), sn(2, 1), False), sn(3, 1), False)
        Cells(1, 8).Resize(UBound(sp) + 1) = Application.Transpose(sp)
    End Sub
    or:
    Code:
    Sub M_snb_002()
        sn = Range("B1:B3")
        sp = Columns(1).SpecialCells(2)
        
        For j = 1 To UBound(sp)
            If (sp(j, 1) = sn(1, 1)) + (sp(j, 1) = sn(2, 1)) + (sp(j, 1) = sn(3, 1)) = 0 Then c00 = c00 & "_" & j
        Next
    
        sp = Application.Index(sp, Split(Mid(c00, 2), "_"), 1)
        Cells(1, 10).Resize(UBound(sp)) = Application.Transpose(sp)
    End Sub
    Does this amendment make any difference ?

    Code:
    Sub M_snb()
      sn = Range("B1:B3")
    
      For Each it In sn
        Columns(1).specialcells(2).Replace it, "", 1
      Next
    
      columns(1).specialcells(4).entirerow.delete
    End Sub
    Last edited by snb; 06-07-2013 at 12:26 PM.

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Persistence paid off!

    Your first rountine (the one with the three Filter and two Transpose calls) bounces between 0.02 and 0.03 seconds, mostly 0.02, so it matches the speed of my routine using only three active lines of code (eight lines of code less than mine)! Congratulations! I was a little surprised that those two Transpose calls did not slow things down more; actually, I would have thought the three nested Filter calls might have slowed thing down as well... obviously I would have been wrong. Again, well done. By the way, your other two attempts did not fair anywhere near as well... the second one taking about 0.14 seconds and the last on about 0.85 seconds. Oh, just for information sake, from the list of 10001 continent names, 2820 of them were being deleted.

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Hi Rick

    Thanks for testing !
    Would disabling screenupdating and calculation make any difference in the last suggestion (containing 'replace it,"" ' )?
    Last edited by snb; 06-07-2013 at 03:16 PM.

  10. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    Hi Rick

    Thanks for testing !
    Would disabling screenupdating and calculation make any difference in the last sugestion (containing 'replace it,"" ' )?
    No, disabling the screen had no real effect (time-wise)

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
  •