delete rows that do not contain the text from a specific cell

Hi all,

Just like I mentioned in the topic: I need a macro that will delete rows that do not contain the text from a specific cell. It should delete rows from range A4:A22, and the text is in a cell A27.
So lets say that there is a text "Venray" in cell A27, this text is also in cells A12 and A20; I want to leave them as they are, but want to delete all other ones from the range of A4:A22.

I found a code that almost solves my problem, but I still need to change it a little bit:

Sub delete_It()

Sheets("Sheet1").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If Cells(r, 1).Value <> "Sarasin" Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


could you help me with that? Thanks in advance!

there is one more thing to do here: how to apply this code to other sheets?
I mean: delete also rows from in sheet2, column B, that does not contain a value indicated in a cell A27 in sheet1.
The range is changed in sheet2: B3:B40
I have 6 sheets in total to deal with.

Thanks in advance!

thanks HaHoBe, now I understood how it should be done. Other users helped me with that in another topic, but your solution also works.