OK, hopefully you fine folks can help me out with this one. Sheet 1 contains all product that was in house 1 week ago. Column C on this page lists the product by serial number and this list can be as short as 5 lines or as long as 500.
Now, Sheet 3 contains the exact information, however; it's the current product in house, column C is still the serial number list and the length also varies.
Sheet 2 is the archive sheet.
I need something that will take anything that is on Sheet 1 but NOT on sheet 3 (these will be the units that have sold), remove it from Sheet 1 and paste it on Sheet 2 at the next available line.
Honestly there are more questions regarding this workbook, but I'll tackle those after I get this figured out! [ATTACH]n1203435[/ATTACH]
Thanks!
Compare and remove
-
Watchdawg72 -
May 10, 2018 at 4:25 PM -
Thread is marked as Resolved.
-
-
-
Try:
Code
Display MoreSub CompareLists() Application.ScreenUpdating = False Dim LastRow As Long LastRow = Sheets("Previous Weekly").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Dim Rng As Range, RngList As Object Dim x As Long Set RngList = CreateObject("Scripting.Dictionary") With Sheets("Weekly Load") For Each Rng In .Range("C2", .Range("C" & .Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng End With With Sheets("Previous Weekly") For x = LastRow To 2 Step -1 If Not RngList.Exists(Cells(x, 3).Value) Then Rows(x).EntireRow.Copy Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "A").End(xlUp).Offset(1, 0) Rows(x).EntireRow.Delete End If Next x End With RngList.RemoveAll Application.ScreenUpdating = True End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!