Exclude Contents From List :


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 🙂

Image

Sub ExcludeFromList()

 

    Dim objDicMap As Object

    Dim VarArrData

    Dim VarArrResult

    Dim rngCell   As Range

    Dim lngCOunt As Long  

    VarArrData = Intersect(Range(“rngRange”).CurrentRegion, Range(“rngRange”).CurrentRegion.Offset(1))

    Set objDicMap = CreateObject(“Scripting.Dictionary”)    ‘ Dictionary Object get Mapping

    ‘Filling Dictionary

    For Each rngCell In Intersect(Range(“MapDelete”).CurrentRegion, Range(“MapDelete”).CurrentRegion.Offset(1))

        If Not objDicMap.exists(rngCell.Value) Then objDicMap.Add rngCell.Value, rngCell.Value

    Next rngCell

    ‘Filling Result Array From Data List which would not Include Mapping Data

    For lngCOunt = LBound(VarArrData) To UBound(VarArrData)

        If Not objDicMap.exists(VarArrData(lngCOunt, 1)) Then

            If Not IsArray(VarArrResult) Then

                ReDim VarArrResult(0 To 0)

                VarArrResult(0) = VarArrData(lngCOunt, 1)

            Else

                ReDim Preserve VarArrResult(UBound(VarArrResult) + 1)

                VarArrResult(UBound(VarArrResult)) = VarArrData(lngCOunt, 1)

            End If

        End If

    Next lngCOunt

    ‘Clear Old List

    Range(“rngRange”).CurrentRegion.Offset(1).Clear

    ‘Replace with new list

    Range(“rngRange”).Offset(1).Resize(UBound(VarArrResult)).Value = Application.Transpose(VarArrResult)

    Set objDicMap = Nothing

    Set rngCell = Nothing

End Sub

Thanks for Reading 🙂

Rajan.

2 Comments Add yours

  1. JohnS says:

    Don’t you need a +1 in the final transpose?

    E.g.
    If isempty(VarArrResult) then
    debug.print “none missing”
    else
    Range(“rngRange”).Offset(1).Resize(UBound(VarArrResult) +1 ).Value = Application.Transpose(VarArrResult)
    end if

  2. hmm.. you are right. thanks john for point out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s