0

In theory this code works but you have to run it 3 or 4 times to get everything removed. I want a code that removes work orders from the master sheet if they are not found on the current work orders worksheet(in progress sheet) and add them to an archive sheet. It seems to only remove every second work order that it should each time. Meaning if I run this code 3 times everything is eventually removed and added to archive as required.

Sub test()
Dim ws As Worksheet
Set ws = Worksheets("In Progress Workorders")
Dim ws2 As Worksheet
Set ws2 = Worksheets("MASTER")
Dim ws3 As Worksheet
Set ws3 = Worksheets("archive")
 
LastRow3 = ws3.UsedRange.Rows.Count + 1
Dim j As Integer
For j = 3 To 100
    If Application.WorksheetFunction.CountIf(ws.Range("C4:C100"), ws2.Cells(j, 2)) <= 0 Then
   
        ws3.Cells(LastRow3, 2) = ws2.Cells(j, 2)
        ws3.Cells(LastRow3, 1) = ws2.Cells(j, 1)
        ws2.Rows(j).EntireRow.Delete
        LastRow3 = LastRow3 + 1
        End If
    Next
 
End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
user21920024
  • 13
  • 3
  • 2
    `For j = 100 To 3 Step -1` – Scott Craner May 18 '23 at 14:03
  • 3
    The short answer is: when deleting rows that you are iterating over, iterate backwards. The medium/guidance is: as a programming paradigm, any time you are deleting items of a collection you are iterating over, expect strange results. There's usually a correct/supported way of doing it, and many wrong ways of doing it. – JNevill May 18 '23 at 14:16

0 Answers0