Deleting specific rows

[GraceTilly]

I have a face recognition device that biometrically checks people in and out of work and at the end of each month I download the data onto an excel spreadsheet.
My issue is that during the course of one month if the facial recognition does not immediately recognise the face it returns the word Undefined in column F, instead of the words, "Check in" or "Check out". What I am hoping to do is highlight the sheet and delete all rows that have the word Undefined in column F. Is this possible? To do each sheet (twenty employees) individually row by row would be hugely time consuming.

 

[ranman256]

paste into module , then run: DeleteBadRows

Public Sub DeleteBadRows()
Dim i As Integer
Dim iRows As Integer
On Error Resume Next

Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count
Cells(iRows, 1).Select

While ActiveCell.Row > 1
   GoSub check2Delete
   ActiveCell.Offset(-1, 0).Select     'prev row
Wend
GoSub check2Delete
Exit Sub

check2Delete:
  If InStr(ActiveCell.Offset(0, 5).Value, "Undefined") > 0 Then
  Rows(ActiveCell.Row).Delete
  End If
Return
End Sub

 

[mumps]

Another option:

Sub DeleteRows()
  On Error Resume Next
  Columns("F").Replace "Undefined", "#N/A", xlWhole, , False
  Columns("F").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[GraceTilly]

paste into module , then run: DeleteBadRows

Public Sub DeleteBadRows()
Dim i As Integer
Dim iRows As Integer
On Error Resume Next

Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count
Cells(iRows, 1).Select

While ActiveCell.Row > 1
   GoSub check2Delete
   ActiveCell.Offset(-1, 0).Select     'prev row
Wend
GoSub check2Delete
Exit Sub

check2Delete:
  If InStr(ActiveCell.Offset(0, 5).Value, "Undefined") > 0 Then
  Rows(ActiveCell.Row).Delete
  End If
Return
End Sub

Thank you so much, works like a charm and will save me hours.

 

[GraceTilly]

Another option:

Sub DeleteRows()
  On Error Resume Next
  Columns("F").Replace "Undefined", "#N/A", xlWhole, , False
  Columns("F").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

Thank you so much you and this website have been a life saver.

 

[Joe4]

I would highly recommend the second code over the first.
Selecting cells in loops will unnecessarily slow your code down, and is usually unnecessary.
Loops in general are slow as it is, and they are not even needed here (as shown in the second code).

 

[mumps]

You are very welcome.