Site of Cao Shouguang, English to Chinese translator

Excel VBA: move matching rows to another sheet

This VBA moves my paid jobs in my job list sheet (sheet1) to the sheet named “history” (sheet4).

If the value of sixth column (Column F) of a row in sheet1 is “closed”, that row is then moved to the end of sheet4.

Sub Button_Remove_Closed_Click()
With Sheet1
'    For Each rw In .UsedRange.Rows
'        If rw.Cells(6).Value = "Closed" Then
'            Target = Sheet4.UsedRange.Rows.Count + 1
'            rw.Copy Destination:=Sheet4.Rows(Target)
'            rw.Delete
'        End If
'    Next
'the above doens't work well as when for example row 5 is deleted, the old row 6 becomes row 5.
'this works 🙂
Dim i, j

j = .UsedRange.Rows.Count

For i = 1 To j
    If .Cells(i, 6).Value = "Closed" Then
        Target = Sheet4.UsedRange.Rows.Count + 1
        .Rows(i).Copy Destination:=Sheet4.Rows(Target)
        i = i - 1
    End If
End With
End Sub

Leave a Comment