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]

With autofilter you can only filter on a maximum of 2 values unless you are doing an exact match.
If you are trying to clear everything except the Total rows try filtering on <>Total

 

[Tash Point O]

With autofilter you can only filter on a maximum of 2 values unless you are doing an exact match.
If you are trying to clear everything except the Total rows try filtering on <>Total

I tried using the "<>Total" row as criteria but it deletes my whole sheet even though I specified the column :

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

 

[MARK858]

Try...

.AutoFilter 1, "<>Total*"

 

[Tash Point O]

Try...

.AutoFilter 1, "<>Total*"

Hi Mark,

This worked, however too well. What happens is that the macro erases everything on the sheet that isn't "total", leaving only total and numeric cells. I'm not sure why it isn't targeting col A and then the row associated with the non total word in col A

 

[Fluff]

Can you please explain exactly what you are trying do?

 

[Tash Point O]

Can you please explain exactly what you are trying do?

Of course. Note that I do have a working (recorded) macro for this, however I wanted to make the macro more efficient.

I would like to identify the words "ACM" and/or, "PAL", and/or "EM" in Col A, then clear contents (or delete) that entire row.

As you mentioned I cannot have more than 2 criteria, therefore I changed the criteria to "<>Total*" per the other suggestion and now the macros is clearing contents on the whole sheet if any cell does not match the criteria. And I'm dumbfounded as to why

 

[MARK858]

Post the exact whole code you are currently using (please put it in code tags as well, paste the code in the thread, select it and then click the # icon).

 

[Tash Point O]

Post the exact whole code you are currently using (please put it in code tags as well, paste the code in the thread, select it and then click the # icon).

Ok NP. TY for explaining how to post a code!! This is the exact code I have been using:

Sub Clear_Methodolody_Lines()






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


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


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


End Sub

 

[MARK858]

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