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() * *** […]http://stats.wordpress.com/b.gif?hos...f=&feed=1

Exclude Contents From List :

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.

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

---

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=320960#p320960
https://eileenslounge.com/viewtopic.php?p=320957#p3209573
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Quote:

---

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.

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.

Quote:

---

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.

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

---

Persistence paid off!:applause:

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!:thumbsup: 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.

Hi Rick

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

Quote:

---

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)