Delete all rows after last one with criteria

[sharky12345]

Can anyone show me how I can delete all the rows after the last one found in column C with the criteria 'Not Available'?

 

[Michael M]

try

Sub MM1()
Dim lr1 As Long, lr2 As Long
lr1 = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lr2 = Cells.Find("Not Available", , xlValues, , xlRows, xlPrevious).Row
Rows(lr2 + 1 & ":" & lr1).EntireRow.Delete
End Sub

 

[Michael M]

EDIT....in case of "not available" in other columns

Sub MM1()
Dim lr1 As Long, lr2 As Long
lr1 = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lr2 = Range("C1:C" & lr1).Find("Not Available", , xlValues, , xlRows, xlPrevious).Row
Rows(lr2 + 1 & ":" & lr1).EntireRow.Delete
End Sub

 

[sharky12345]

Perfect, thanks Michael.

 

[Michael M]

Glad to help...