Exclude Contents From List :

**Rajan_Verma** · 06-06-2013, 07:53 PM

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 :

**Rick Rothstein** · 06-06-2013, 11:58 PM

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.

**snb** · 06-07-2013, 12:28 AM

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

**Rick Rothstein** · 06-07-2013, 12:50 AM

Originally Posted by snb

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.

**snb** · 06-07-2013, 01:08 AM

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.

**Rick Rothstein** · 06-07-2013, 01:28 AM

Originally Posted by snb

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.

**snb** · 06-07-2013, 12:08 PM

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

**Rick Rothstein** · 06-07-2013, 12:47 PM

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.

**snb** · 06-07-2013, 01:29 PM

Hi Rick

Thanks for testing !
Would disabling screenupdating and calculation make any difference in the last suggestion (containing 'replace it,"" ' )?

**Rick Rothstein** · 06-07-2013, 01:53 PM

Originally Posted by snb

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)