Advance Filter Macro

[Negi1984]

Hi All,

I have data in below format :-
Now I want to add a filter as below mentioned criteria :-

1) Picklisted : Should be New
2) 410(A) : Should remove latest 6 dates from filter
3) GR1 : Should be False
4) GR01 : Should be select all numbers & numbers where 2 numbers mentioned with text Rej.

I tried by myself with without macro with advance filter , but unable to found any solution, how to filter out latest 6 dates from filter. Please assist.
[TABLE="width: 378"]
<tbody>[TR]
[TD]State[/TD]
[TD] Program[/TD]
[TD]Picklisted[/TD]
[TD]410 (A)[/TD]
[TD]GRO1[/TD]
[TD]GR1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]A[/TD]
[TD]New[/TD]
[TD]7-Aug-13[/TD]
[TD]2334[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]NSW[/TD]
[TD]B[/TD]
[TD]Old site[/TD]
[TD]24-Jul-13[/TD]
[TD]2442[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]NSW[/TD]
[TD]C[/TD]
[TD]New[/TD]
[TD]21-Oct-15[/TD]
[TD]3444[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]NSW[/TD]
[TD]D[/TD]
[TD]New[/TD]
[TD]15-Jan-15[/TD]
[TD]2345[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]NSW[/TD]
[TD]E[/TD]
[TD]Old site[/TD]
[TD]3-Sep-15[/TD]
[TD]5223[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]G[/TD]
[TD]New[/TD]
[TD]11-Jun-14[/TD]
[TD]71,74 Rej[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]QLD[/TD]
[TD]D[/TD]
[TD]Old site[/TD]
[TD]29-Aug-13[/TD]
[TD]4555[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]QLD[/TD]
[TD]S[/TD]
[TD]New[/TD]
[TD]20-Sep-13[/TD]
[TD]31,74, Rej[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]QLD[/TD]
[TD]SS[/TD]
[TD]Old site[/TD]
[TD]15-Jul-13[/TD]
[TD]7470[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]QLD[/TD]
[TD]ER[/TD]
[TD]Old site[/TD]
[TD]16-Jul-13[/TD]
[TD]7470[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]WA[/TD]
[TD]4[/TD]
[TD]New[/TD]
[TD]17-Sep-13[/TD]
[TD]74,Rej[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]WA[/TD]
[TD]56[/TD]
[TD]Old site[/TD]
[TD]12-Aug-13[/TD]
[TD]7470[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]

 

[Marcelo Branco]

Rajender,

Delete the previous version of the function EvalCell and paste this new version

Function EvalCell(r As Range)
    Dim spl As Variant, i As Long, lCounter As Long
    
    If Application.IsNumber(r.Value) Then
        EvalCell = "Y"
    ElseIf InStr(1, r.Value, "Rejected", vbTextCompare) > 0 Then
            spl = Split(Application.Trim(Replace(Replace(Replace(r, "-", " "), ",", " "), "Rejected", "")))
            For i = LBound(spl) To UBound(spl)
                If IsNumeric(spl(i)) Then lCounter = lCounter + 1
            Next i
            If lCounter > 1 Then
                EvalCell = "Y"
            Else
                EvalCell = "N"
            End If
    Else
            EvalCell = "N"
    End If
End Function

Hope this works ray:

M.

 

[Marcelo Branco]

Use the code in post 19 to run the macro AdvFilter with the function EvalCell above (new version)

M.

 

[Negi1984]

Use the code in post 19 to run the macro AdvFilter with the function EvalCell above (new version)

M.

Thanks a ton Marcelo, its all working fine now . I will try to learn , how it works.
Have a nice day to you.

Regards,
Rajender

 

[Marcelo Branco]

Thanks a ton Marcelo, its all working fine now . I will try to learn , how it works.
Have a nice day to you.

Regards,
Rajender

You are very welcome. Glad to help

M.