Delete Rows based on data in Column

[mrsushi]

The below is working fine to delete the rows based on the condition in column N . However, it takes too long if one has 10,000+ rows of data.

Sub Delete()
Last = Cells(Rows.Count, "N").End(xlUp).Row
For i = Last To 3 Step -1
If (Cells(i, "N").Value) <> "NOT ON FILE" Then
'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
Cells(i, "N").EntireRow.Delete
End If
Next i
End Sub





I have tried the below code but it appears to be stuck on the code - Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete.
Any ideas?

With ActiveSheet.UsedRange
.AutoFilter 14, "=NOT ON FILE"
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub


Also, I've tried using the auto filter method to delete every row that doesn't contain "NOT ON FILE", so failing to get this working
Rows("2:2").AutoFilter Field:=14, Criteria1:="NOT ON FILE", VisibleDropDown:=False

 

[Fluff]

How about

Sub mrsushi()
   With ActiveSheet
      .Range("A2:N2").AutoFilter 14, "Not on file"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[mrsushi]

How about

Sub mrsushi()
   With ActiveSheet
      .Range("A2:N2").AutoFilter 14, "Not on file"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

That's done the trick. However, I need to keep the rows that contain "Not on file". Every other row has deleted so I need it to work the other way

 

[Fluff]

In that case use

.Range("A2:N2").AutoFilter 14, "<>Not on file"

 

[mrsushi]

In that case use

.Range("A2:N2").AutoFilter 14, "<>Not on file"

That's perfect Thanks again.

 

[Fluff]

Glad to help & thanks for the feedback.