Autofilter: 1 Field, Multiple Words - NOTHING WORKS

[Tash Point O]

Hello All,

I've been racking my brains trying to shorten a recorded autofilter macro. I recorded the macro 3x's because I have 3 words I need it to find, then it clears entire rows when it finds that word.

I want to make it more compact and put all 3 criteria(words) into 1 and not have 3 different subroutines. I've tried it all, including using Operator:= xlAnd, and then trying xlOr. Nothing is working

Note that the recorded macro uses wildcard, not sure why. I don't need that.

The words are ACM, EM, PAL

Here is what the Data Set looks like:

 

[Fluff]

How about

Sub Clear_Methodolody_Lines()
With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Range("A1", .Range("A" & Rows.count).End(xlUp))
        .AutoFilter 1, "*ACM*", xlOr, "*EM*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.ClearContents
        On Error GoTo 0
        .AutoFilter 1, "*PAL*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.ClearContents
        On Error GoTo 0
    End With
    .AutoFilterMode = False
End With

End Sub

 

[Tash Point O]

Sorry can you also post the code you used that cleared cells in the other columns when using Total*

Absolutely:

Sub newway()
With ActiveSheet
With Range("A1", Range("A" & Rows.Count).End(xlUp))
 
        .AutoFilter 1, "<>Total*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.ClearContents
    End With
    .AutoFilterMode = False
End With
End Sub

 

[Tash Point O]

How about

Sub Clear_Methodolody_Lines()
With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Range("A1", .Range("A" & Rows.count).End(xlUp))
        .AutoFilter 1, "*ACM*", xlOr, "*EM*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.ClearContents
        On Error GoTo 0
        .AutoFilter 1, "*PAL*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.ClearContents
        On Error GoTo 0
    End With
    .AutoFilterMode = False
End With

End Sub

This worked! I really wonder when I will get good enough to play around with syntax to come up with work arounds, like this. So frustrating. Thank you for cracking the case. =)

 

[Fluff]

Glad we could help & thanks for the feedback