Autofilter: 1 Field, Multiple Words - NOTHING WORKS

Tash Point O

New Member
Joined
Feb 12, 2018
Messages
47
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.

2z84zz5.jpg


The words are ACM, EM, PAL

Here is what the Data Set looks like:

2klg8k.jpg
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,429
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 12, 2018
Messages
47
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 :ROFLMAO::

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,036
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try...

Code:
.AutoFilter 1, "<>Total*"
 

Tash Point O

New Member
Joined
Feb 12, 2018
Messages
47

ADVERTISEMENT

Try...

Code:
.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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,429
Office Version
  1. 365
Platform
  1. Windows
Can you please explain exactly what you are trying do?
 

Tash Point O

New Member
Joined
Feb 12, 2018
Messages
47

ADVERTISEMENT

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 :confused:
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,036
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Feb 12, 2018
Messages
47
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:

Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,036
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Sorry can you also post the code you used that cleared cells in the other columns when using Total*
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,316
Messages
5,635,521
Members
416,862
Latest member
MGDlite

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top