VBA to delete range of cells that don’t contain certain text

[SJ1981]

Hello I have a cell range C5:F34
Each row relates to same record with a different column heading C:F. I would like to be able to clear all cells except for the rows that have “Not Arrived” or “DCase” in column F.
I have made a start with the below however it only deletes the contents of cell F & not C:E aswell? I’m also not sure how to add “DCase” into the criteriacell.Value section, can only get it to work with one or the other.
many thanks for any help that can be given.

TEST Macro
'
Dim criteriarange As Range

Dim criteriacell As Range

Set criteriarange = Range("F5:F34")

For Each criteriacell In criteriarange

If Not criteriacell.Value = "Not Arrived" Then

criteriacell.ClearContents

End If

Next criteriacell

End Sub

 

[Fluff]

How about

Sub SJ1981()
   With ActiveSheet
      .Range("C4:F34").AutoFilter 4, "Not Arrived", xlOr, "DCase"
      .AutoFilter.Range.Offset(1).Value = ""
      .AutoFilterMode = False
   End With
End Sub

 

[SJ1981]

Thank you - this very nearly does the right thing.... it’s the wrong way round though it clears not arrived & DCase rather than everything but these..... so presumably I just need to change the “ “ references to the other options? Thanks

 

[SJ1981]

I tried changing it to all options except Not arrived & DCase but there was an error as too many arguments?

 

[Fluff]

In that case how about

Sub SJ1981()
   With ActiveSheet
      .Range("C4:F34").AutoFilter 4, "<>Not Arrived", xlAnd, "<>DCase"
      .AutoFilter.Range.Offset(1).Value = ""
      .AutoFilterMode = False
   End With
End Sub

 

[SJ1981]

It worked!
Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback