VBA Delete Rows incl Case Sensitive

[horizonflame]

Hi All,

I am looking to delete rows containing the letter "S" in column C, and containing "cost", "ref" or "spare" anywhere in the string. I think I have the code sorted from research but can you help make it ignore case sensitivity?

Thanks

Sub test()
    Application.ScreenUpdating = False  
    Dim i&

i = Range("D" & Rows.Count).End(xlUp).Row
While i <> 1
With Cells(i, 4)
If (.value Like "*cost*" _
Or .value Like "*ref*" _
Or .value Like "*spare") _
Then
Rows(i).Delete
End If
End With
        i = i - 1
    Wend
    Dim i&

i = Range("C" & Rows.Count).End(xlUp).Row
While i <> 1
With Cells(i, 3)
If .value Like "*S*" _
Then
Rows(i).Delete
End If
End With
        i = i - 1
    Wend

    Application.ScreenUpdating = True

 

[Fluff]

How about

Sub horizonflame()
    With ActiveSheet
        .Range("A1:D1").AutoFilter 4, "*s*", xlOr, "*ref*"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub

 

[horizonflame]

Hi Fluff,

Thank you but that misses off my other criteria. (I should have put "cost", "ref" or "spare" is in Column D and "*s*" in Column C)

Thanks

 

[Fluff]

Apologies, missed the cat that S was in a different column.
What is the last column in your data & is that fixed?

 

[horizonflame]

Hi @Fluff, apologies for my oversight!

The last column is fixed and its Column W.

Thanks

 

[Norie]

Try this.

Sub test()
Dim I As Long

    Application.ScreenUpdating = False

    I = Range("D" & Rows.Count).End(xlUp).Row
    While I <> 1
        With Cells(I, 4)
            If (UCase(.Value) Like "*COST*" _
                Or UCase(.Value) Like "*REF*" _
                Or UCase(.Value) Like "*SPARE") _
                Or UCase(.Offset(, -1).Value) Like "*S*" _
                Then
                Rows(I).Delete
            End If
        End With
        I = I - 1
    Wend

    Application.ScreenUpdating = True

End Sub

 

[Fluff]

Ok, how about

Sub horizonflame()
    With ActiveSheet
        .Range("X2:X" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=OR(ISNUMBER(SEARCH(""S"",C2)),ISNUMBER(SEARCH({""*ref*"",""*spare*"",""*cost*""},D2)))"
        .Range("A1:X1").AutoFilter 24, True
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .AutoFilterMode = False
        .Range("X:X").ClearContents
    End With
End Sub

 

[horizonflame]

@Norie and @Fluff, many thanks for your help!

 

[Fluff]

Glad we could help & thanks for the feedback