Replace Macro recorded code with more effective code

Hey

I have the following code that was recorded through the Macro record function. The goal was to exclude the cells that had the word "PRE" in it. At the time I recorded the Macro there was only a few things in the list other than "PRE". If someone adds a value not in the code and the Macro is run, the new value is also excluded from the filtered list.

How can I filter $A$1:$P$25001 to exclude only values of "PRE" contained in E:E ?

Here is the code from the Macro

    ActiveSheet.Range("$A$1:$P$25001").AutoFilter Field:=5, Criteria1:=Array( _
        "?", "??", "???", "Complete", "FC", "N/A", "POST", "="), Operator:=xlFilterValues

Hi, scaffdog845,

AFAIR no direct way to do so but you may consider using either building up a Dictionary of unique items, a list in another sheet to refer to or a helper column to filter by.

Ciao,
Holger

Thanks Holger. I thought just about anything was feasible with VBA and here I though this request would be an especially easy one for the VBA gurus out there.

Hi, scaffdog845,

have you had a look at the Advanced Filter which will perform as you require (sorry I donīt know a way to exclude terms with AutoFilter except what I indicated).

BTW: could you please update your signature - the scales are gone.

Ciao,
Holger

scaffdog, you could try something like this. Basically you are adding a column to the right of your data, I usually call it "Exclude", then the macro puts a 1 on each row you want to ignore based on an array of values you supply. In this case, if there are any cells in E:E = "PRE", they will be filtered out. I don't know if you want to look for any instances, i.e. you would filter out a value of "PREVIOUS", because it contains "PRE", or just keep it at exact match. Either way it would be easy to tweak. Now, this is very much a "brute force" method, so it may not be the most efficient, but it works.

Sub Filter()

    Dim arFilters() As Variant
    Dim ws As Worksheet
    Dim rng As Range
    Dim c As Variant
    Dim i As Long
    
    arFilters() = Array("PRE", "OTHER", "ANOTHER")
    
    Set ws = ActiveWorkbook.ActiveSheet
    
    Set rng = ws.Range("E2:E" & ws.Cells.SpecialCells(xlCellTypeLastCell).Row)
    
    For Each c In rng
        For i = 0 To UBound(arFilters()) - 1 Step 1
            If c.Value = arFilters(i) Then
                c.Offset(0, 12).Value = 1
                Exit For
            End If
        Next i
    Next c
    
    ActiveSheet.Range("$A$1:$Q$" & ws.Cells.SpecialCells(xlCellTypeLastCell).Row).AutoFilter Field:=17, Criteria1:="="

End Sub

Greg

Originally Posted by swade730

Is the ws in this code referring to worksheets?

Set rng = ws.Range("E2:E" & ws.Cells.SpecialCells(xlCellTypeLastCell).Row)

???

Yes, and it was referenced in the line above. Probably not necessary here, because it did not save too much typing, but I usually set the reference at the beginning because I may need to reference it later. Just my preference, but not always necessary.

Greg

Originally Posted by Sbarro79

I just wonder: does this

 ActiveSheet.Range("$A$1:$P$25001").AutoFilter Field:=5, Criteria1:="<>PRE", Operator:=xlFilterValues

solve the problem?

Indeed it does work. I only offered the solution I did because it sounded like the conditions were going to grow. I had to import some messy data from a materials database one time, and I literally had to filter out hundreds of values. The only way I could come up with doing it was as I proposed above. However, instead of an array, I simply put my filter out words in another sheet. Anyhow, I think the OP should be okay now.

Hey gang. Thanks for the addition comebacks. I thought I was dead in the water after seeing HaHoBe's response. Fortunately I was able to use a helper column that looked for the value "PRE" and if it existed showed the value defaulted to "HIDE" and if it didn't it defaulted to "SHOW" Pretty simple sort after that.

I'll definitely try the additional suggestions as a learning opportunity.

Take care