CODEWITHSUNDEEP
excelvba
Andy5
Andy5

Reputation: 2405

Using AutoFilter with Criteria

I have hit a brick wall on this one, but I would like someone who can help me with autofilter and perhaps advanced filter (but not sure on this one) where I have one column that contains dates which also contains the value 'NULL' and another column that contains numbers plus the value 'NULL'. In this second column there can be duplicate numbers.

My criteria for the first column is to exclude the value 'NULL', but to also exclude dates less than 14. The criteria for the second column is to include ALL 'NULL' values, but where there are numbers ONLY to show unique numbers (or distinct in proper speak).

I am unsure how to do this and I haven't been able to find anything on the internet.

Can anyone help?

Upvotes: 1

Views: 6108

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

I used the macro recorder to get this code:

ActiveSheet.Range("$A$1:$B$6").AutoFilter Field:=2, Criteria1:="<>NULL", _
    Operator:=xlAnd, Criteria2:="<5/14/2012"

That said, you will need to do this:

   ActiveSheet.Range("$A$1:$B$6").AutoFilter Field:=2, Criteria1:="<>NULL", _
        Operator:=xlAnd, Criteria2:="<" & Date - 14

For the 2nd filter. Copy the column to a blank column and use the RemoveDuplicates function to get the Distinct List of numbers.

Intersect(UsedRange, Range("A1")).EntireColumn.Copy Range("Z1")
Intersect(UsedRange, Range("Z1")).EntireColumn.RemoveDuplicates(more args here that you will need to fill)

Upvotes: 1

Related Questions