Deleting Rows based on a criteria

[LesKing]

Hi all,

I have a spreadsheet with 88 rows (which will get more rows over time). The data starts at row 2 (row 1 is a header row). Column 'I' in each row shows either "Mandatory" or "Not Required". I need to delete all the rows where Column 'I' contains the words "Not Required". I can do this manually, but would like to automate the process using a macro. I've tried and failed to write a successful one. Can anyone advise please? Thank you.

Les King

[#2]

@LesKing 

Sub DeleteNotRequired()
    Application.ScreenUpdating = False
    Range("I:I").AutoFilter Field:=1, Criteria1:="Not Required"
    Range("I2:I1000").SpecialCells(Type:=xlCellTypeVisible).EntireRow.Delete
    Range("I:I").AutoFilter
    Application.ScreenUpdating = True
End Sub

[#3]

@LesKing 

Sub Delete_not_required()

Dim rng As Range
Dim startData As Variant
Dim resultData As Variant
Dim i As Long
Dim lastrow As Long
Dim k As Long
Dim j As Long

Range("M:U").Clear

lastrow = Range("B" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:I" & lastrow)

startData = rng.Value
ReDim resultData(1 To rng.Rows.Count, 1 To 9)

k = 1

For i = 1 To UBound(startData, 1)
    If startData(i, 9) <> "Not required" Then
    For j = 1 To 9
       resultData(k, j) = startData(i, j)
       
    Next j
    k = k + 1
    End If
    
Next i

Range("M2").Resize(k, 9) = resultData

End Sub

[#4]

Thank you, Hans - you've rescued me again!!
Les King

[#5]

Thanks for your reply Oliver. I had already got a reply from Hans Vogelaar which worked perfectly, doing exactly what I needed. So I'm going with that. But thank you so much for all your effort.
Les King