Sub Compare_Columns_A_and_B_with_Arrays() Dim wb As Workbook, ws As Worksheet, Missing As Worksheet Set wb = ActiveWorkbook Set ws = wb.Worksheets("Sheet1") Set Missing = wb.Worksheets("Missing") Dim lRowA As Long lRowA = ws.Cells(Rows.Count, 1).End(xlUp).Row Dim sourceArray As Variant, srcrng As Range Set srcrng = ws.Range("A2:A" & lRowA) sourceArray = RangeToArray(srcrng) Dim lRowB As Long lRowB = ws.Cells(Rows.Count, 2).End(xlUp).Row Dim verifyArray As Variant, verifyrng As Range Set verifyrng = ws.Range("B2:B" & lRowB) verifyArray = RangeToArray(verifyrng) Dim lRowM As Long Missing.Range("A1").Value = "ID's Missing from Results" For Each arrval In sourceArray IsInArray = (UBound(Filter(verifyArray, arrval)) > -1) If IsInArray = False Then 'Debug.Print arrval lRowM = Missing.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row Missing.Range("A" & lRowM).Value = arrval End If Next arrval End Sub Public Function RangeToArray(rng As Range) As Variant Dim i As Long, r As Range ReDim arr(1 To rng.Count) i = 1 For Each r In rng arr(i) = r.Value i = i + 1 Next r RangeToArray = arr End Function
Welcome to FreeSoftwareServers Confluence Wiki
Overview
Content Tools