Comparing Column A to B and B to A and Copy Entire Row of Missing and Added to New Sheets Respectively

Question

I'm trying to figure out the best way to attack this problem and my head is spinning a bit, I'm not sure if I should use For Each Cell or Arrays or Collections to do some comparisons and copy entire rows to new sheets. I'd like to use Arrays but my code only uses the values of column but then I have to go back and re-loop through column to find "missing values" and copy entire row which seems to defeat part of the point of using arrays (speed/efficiency).

I'm looking for advice on the best way to tackle this issue, but I'll post my array code as well.

First off, example data:

Sheet1:

Sheet2:

The idea is Sheet1 is yesterdays report and sheet2 is todays.

My goal is two more sheets (or one combo sheet, but that seems unnecessary hard as I need to do total calculations on each result sheet search results respectively by totaling one of the columns, but not the value in column A)

ItemsAdded:

A6 AV6

ItemsRemoved:

A5 AV5

So basically it is finding what items where removed and what was added comparing sheet2 to sheet1 column A.

So far I was able to get that part, without the row values and I'm really wondering if I'm attacking this correctly.

IE: This gets the missing/added items. Now I need to go and fetch the entire row for the values in each sheet, but am unsure how and the code is starting to look long and repeating.

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
Public Sub Compare_Columns_A_and_B_with_Arrays()

 Dim wb As Workbook, wsA As Worksheet, wsB As Worksheet, Missing As Worksheet, Added As Worksheet
 Set wb = ActiveWorkbook
 Set wsA = wb.Worksheets("Sheet1")
 Set wsB = wb.Worksheets("Sheet2")

 Set Missing = wb.Worksheets("Missing")
 Set Added = wb.Worksheets("Added")

Dim lRowA As Long
 lRowA = wsA.Cells(Rows.Count, 1).End(xlUp).Row
 Dim sourceArray As Variant, srcrng As Range
 Set srcrng = wsA.Range("A1:A" & lRowA)
 sourceArray = RangeToArray(srcrng)

Dim lRowB As Long
 lRowB = wsB.Cells(Rows.Count, 2).End(xlUp).Row
 Dim verifyArray As Variant, verifyrng As Range
 Set verifyrng = wsB.Range("A1:A" & lRowB)
 verifyArray = RangeToArray(verifyrng)



For Each arrval In sourceArray
 IsInArray = (UBound(Filter(verifyArray, arrval)) > -1)
 If IsInArray = False Then
  'Debug.Print arrval
  Dim lRowMissing As Long
  lRowMissing = Missing.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
  Missing.Range("A" & lRowMissing).Value = arrval
 End If
Next arrval

For Each arrval In verifyArray
 IsInArray = (UBound(Filter(sourceArray, arrval)) > -1)
 If IsInArray = False Then
  'Debug.Print arrval
  Dim lRowAdded As Long
  lRowAdded = Added.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
  Added.Range("A" & lRowAdded).Value = arrval
 End If
Next arrval


End Sub

Answer 1

I actuallaly ended up using @AceErno's comment and using AutoFilter to pull the EntireRows of the data that was found by comparing arrays using the code in my original question. I'm not sure happy with my code, but it works and I can look into that later when I am feeling up for it.