VBA Code isn't using the entire data set

hi, this code is working to move any item in the data set with "A380-800" in column 2 to a new sheet however the entirety of column 2 isn't being captured. I have to run the code 3 or 4 times before all of the rows with "A380-800" in column 2 are moved to the new sheet. The data set is quite large at up to 800 or 900 rows.

I used the following piece of code:

Sub Button2_Click()
Application.ScreenUpdating = False
For Each myCell In Sheet1.Columns(2).Cells
If myCell.Value = "A380-800" Then
myCell.EntireRow.Copy Worksheets("A380-800s").Range("A" & Rows.Count).End(3)(2)
myCell.EntireRow.Delete
End If
Next

End Sub

When you are deleting rows, you need to work from the bottom up, otherwise you will skip rows. So, rather than use For Each, you should use a row counter and use something like For i = lLR To 2 Step -1 and refer to Range("B" & i)

I'm guessing it "skips" the ones where A380-800 appears twice in a row because you're deleting the row and then it skips over that one. Try like this instead:

Please Login or Register  to view this content.

WBD