Macro to ignore first row, or move with multiple criteria

[MelissaY1]

I am admittedly ignorant & dumb to Macro's but I am trying to learn so please bear with me!

I have a project tracking spreadsheet with multiple sheets of information for varying stages of the tasks. On the main sheet I have a column that has multiple sets of completion (Complete, Hold, NIGO, In Progress, Waiting for Info). Currently I have a macro that will move & delete anything with "complete" in the column to another sheet. I would like to be able to run a macro that recognizes if a cell has anything in the column. With the help of google I have found a formula that somewhat works BUT when I run the macro, it moves & deletes the header row from my sheet, which I obviously don't want to happen.

How can I either -
1. create a macro that ignores the header row & will move & delete anything that is in column Q
or
2. create a macro that will move & delete multiple criteria for column Q

The macro I am currently running -

Sub move_rows_to_another_sheet()
For Each myCell In Selection.Columns(17).Cells
If myCell.Value = "Complete" Then
myCell.EntireRow.Copy Worksheets("Completed Cases").Range("A" & Rows.Count).End(3)(2)
myCell.EntireRow.Delete
End If
Next
End Sub

 

[Kaper]

How about checking if row is >1 (so skipping header row):

Sub move_rows_to_another_sheet()
For Each myCell In Selection.Columns(17).Cells
If myCell.Row>1 then
  If myCell.Value = "Complete" Then
    myCell.EntireRow.Copy Worksheets("Completed Cases").Range("A" & Rows.Count).End(3)(2)
    myCell.EntireRow.Delete
  End If
end if
Next
End Sub

 

[Alex Blakenburg]

I think the code has bigger issues.
• Selection.Columns(17) is not necessarily column Q it is the 17th column in the selection.
• Deleting from the top down causes syncing issues as you step down the rows since the rows below move every time you delete a row.

Tell us what your "multiple criteria" are so we can modify the If statement. In the meantime see if this helps.
It assumes Row 1 of the sheet is the heading row and starts at row 2.

Sub move_rows_to_another_sheet_Mod()
    Dim wsSrc As Worksheet, wsComplete As Worksheet
    Dim rngSrc As Range, rngDelete As Range
    Dim mycell As Range
    
    Set wsSrc = ActiveSheet
    With wsSrc
        Set rngSrc = .Range(.Cells(2, "Q"), .Cells(Rows.Count, "Q").End(xlUp))
    End With
    
    Set wsComplete = Worksheets("Completed Cases")
    
    For Each mycell In rngSrc.Cells
        If mycell.Value = "Complete" Then
            mycell.EntireRow.Copy wsComplete.Range("A" & Rows.Count).End(3)(2)
            If rngDelete Is Nothing Then
                Set rngDelete = mycell
            Else
                Set rngDelete = Union(mycell, rngDelete)
            End If
        End If
    Next mycell
    
    If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub