Sub Main()
Application.ScreenUpdating = False
Dim stNow As Date
stNow = Now
Dim varr As Variant
Set varr = Selection
MRows = Selection.Rows.Count
If varr.Columns.Count > 1 Then Exit Sub
On Error Resume Next
Dim arr As Variant
T = InputBox("Select start of Second Range", vbOKOnly)
Set arr = Range(T)
With arr
r = .Row
c = .Column
End With
If r = "" Or c = "" Then Exit Sub
Set arr = Cells(r, c).Resize(MRows, 1)
Dim x, y, match As Boolean
For Each y In arr
match = False
For Each x In varr
If y = x Then match = True
Next x
If Not match Then
Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1) = y
End If
Next
Range("B1") = "Items not in A Lists"
Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 2) = "Items not in I Lists"
'Dim arr As Variant
arr = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
'Dim varr As Variant
varr = Range("I3:I" & Range("I" & Rows.Count).End(xlUp).Row).Value
'Dim x, y, match As Boolean
For Each x In arr
match = False
For Each y In varr
If x = y Then match = True
Next y
If Not match Then
Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1) = x
End If
Next
Debug.Print DateDiff("s", stNow, Now)
Application.ScreenUpdating = True
End Sub
Bookmarks