Option Explicit
' set reference to Microsoft Scripting Runtime dll
Public Function NOT_List(list1 As Range, list2 As Range) As Variant
' Inputs: list1 full list
' list2 items to exclude from list1
' output list of items from list1 excluding anything from list2
Dim dFull As Scripting.Dictionary
Dim dExc As Scripting.Dictionary
Dim cell As Range
Dim result() As Variant ' output table
Dim index As Long ' loop counter

Set dFull = New Scripting.Dictionary
Set dExc = New Scripting.Dictionary

' load exclusions
For Each cell In list2.Cells
If Not dExc.Exists(cell.Value) Then
dExc.Add cell.Value, cell.Value
End If
Next

' now build full list, testing for exclusions
For Each cell In list1.Cells
If Not dExc.Exists(cell.Value) Then
' ignore duplicates
If Not dFull.Exists(cell.Value) Then
dFull.Add cell.Value, cell.Value
End If
End If
Next

'dFull is now our adjusted list
' so prepare result
ReDim result(0 To dFull.Count - 1)
For index = 0 To dFull.Count - 1
result(index) = dFull.Items(index)
Next
' transpose make the list vertical for dumping to a worksheet
NOT_List = WorksheetFunction.Transpose(result)

End Function

Method. Using a scripting dictionary from the MS Scripting Runtime DLL is
useful because the dictionary has an Exists method. We can use this to text
if an item is in the list if the item itself is the key too.
So we load a dictioanary object with the exclusions first, then we populate
another disctionary from the full list where the item does not exist in the
exclusions dictionary.

tested Ok




"Herbert Chan" wrote:

Hello,

I've two lists of data in array in VBA.

How do I exclude the data in array1 from array2 so that I will be left with
an array2 that does not contain any number in array1?

I need this function in VBA.

Thanks.

Herbert