Move row to new sheet based on a value and delete row that was moved.

[snelson848]

I've been trying to work though this problem and seem to have most of it solved. I can get excel to move all matching rows to a new sheet based on a value in column 5 or column E. What I would like to happen is that after the matching rows have been moved to sheet two, it deletes the row(s) that were moved from sheet 1. Any thoughts on how I can do this? Here's the code I'm using. Sheet 1 has 5 columns A thru E.

Private Sub CommandButton1_Click()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To a
If Worksheets("Sheet1").Cells(i, 5).Value = "Missing" Then
Worksheets("Sheet1").Rows(i).Copy
Worksheets("Sheet2").Activate
b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet2").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet1").Activate

End If

Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select
End Sub

[AC PORTA VIA]

try

Please Login or Register  to view this content.

[snelson848]

Yes, that worked, I appreciate it. Is there a way to make it move all instances of "Missing" at one time? As your code works, I have to push the button multiple times to move all the lines. If not, I can live with it. Thanks again for your help!

[AC PORTA VIA]

it does when I try

[Fluff13]

When deleting rows like that you need to loop from the bottom up.

Please Login or Register  to view this content.

[Fluff13]

Or a slightly simplified version

Please Login or Register  to view this content.