VBA code to Autofilter for "Everything, but certain values"

[epb613]

Hi I work in Software testing and each morning I like to export the defect log into Excel.

I created a macro to format everything nicely and autofilter for active defects, but I find that every week or two I have another value to the filter list (new defect statutes get created periodically).

What I really want to do is autofilter for everything EXCEPT "Closed" and "Canceled". Is there a way to do that?

Here's my current autofilter line:

    Defect_Table.AutoFilter Field:=2, Criteria1:=Array("In Coding - Tech", _
        "New", "Ready for Retest", "Ready for Tech", "Retest Failed", _
            "Retest Passed", "Watch", "Researching - Tech", _
                "Researching - Admin"), Operator:=xlFilterValues

Instead of adding the values I want shown, I want to add the values that I want hidden, since the shown list keeps changing but the hidden list stays static (just Closed and Canceled). Is this possible?

Thanks!
Pinny

 

[SteveO59L]

Something along the lines of

Dim c As Range, rng
Set rng = Range("a1:a100")
For Each c In rng
If c.Value = "Closed" Or c.Value = "Cancelled" Then
c.EntireRow.Hidden = True
End If
Next c

 

[epb613]

Hey Steve, that's a neat idea, and I'll definitely keep that code in my toolbox for the future.

Here's the "proper" way to do it though. (I found this by using record macro - duh!)

Defect_Table.AutoFilter Field:=2, Criteria1:="<>Closed", Criteria2:="<>Canceled"

 

[epayen]

Thank you very much, very usefull.

 

[Rolly_Sefu]

Hy,

If I would like to filter without 3 criteria ?

Thanks.

Hey Steve, that's a neat idea, and I'll definitely keep that code in my toolbox for the future.

Here's the "proper" way to do it though. (I found this by using record macro - duh!)

Defect_Table.AutoFilter Field:=2, Criteria1:="<>Closed", Criteria2:="<>Canceled"