As an exercise, set up a new sheet with values 1 to 10 in cells C5 and down:
2024-08-01_110939.jpg
Here's the code to copy/paste:
Code:
Sub blah()
For Each empCell In Range(Range("C5"), Range("C5").End(xlDown))
empCell.Select
empCell.EntireRow.Delete
Next empCell
End Sub
Now use
F8 on the keyboard to step through the code, paying particular attention to what's selected (to indicate what's going to be acted upon). The second iteration of the loop doesn't select what you expect; it selects the 3 instead of the 2! This is because the rows have all shifted up when a row is deleted.
Let the macro run to its finish and you're left with:
2024-08-01_111844.jpg
There are several ways to get around this, one of them being to iterate from the bottom of the range up:
Code:
Sub blah2()
Set rngEmpNames = Range(Range("C5"), Range("C5").End(xlDown))
For i = rngEmpNames.Rows.Count To 1 Step -1
rngEmpNames.Cells(i).Select
rngEmpNames.Cells(i).EntireRow.Delete
Next i
End Sub
but this means you end up pasting things in a different order on the destination sheet.
Another way is to iterate as you have done but do no deleting. Instead, mark the rows for deletion later. This doesn't involve actual marking, instead you add the cells, whose rows you want to delete, to a range variable in the code, and when all the copying has been completed, delete those rows.
For example:
Code:
Sub blah3()
Dim rngToDelete As Range
For Each empCell In Range(Range("C5"), Range("C5").End(xlDown))
empCell.Select 'you can lose this line later
If Application.WorksheetFunction.IsEven(empCell.Row) Then 'just a way of not deleting all rows
If rngToDelete Is Nothing Then Set rngToDelete = empCell Else Set rngToDelete = Union(rngToDelete, empCell)
End If
Next empCell
'rngToDelete.Select ' you can include this line if you're stepping through the code with F8 just to show you what rngToDelete is.
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End Sub