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

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.


No comments yet.

Leave a comment