Welcome to FreeSoftwareServers Confluence Wiki

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

  • No labels