Advanced filter with dynamic critieria range

  • Hi everyone,


    I've been looking a lot on this forum lately and decided to ask for help about a problem that's been bugging me for days (first time posting).
    Thanks in advance! Seriously that would be a huge help... im completly stuck.

    Here's the situation:


    - 10 cell long X 20 row large excel table with occasionnal "A" , "B" or "C" in cells. Header is week# (10,11,12...20)
    - I want to filter between any 2 weeks (exemple: all row with "B" between week "10" and "14") specified in 2 designated cells (ex: Start = G1 and End= G2)
    . Here it should show row 2, 3, 4, 7


    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    10

    [/td]


    [td]

    11

    [/td]


    [td]

    12

    [/td]


    [td]

    13

    [/td]


    [td]

    14

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    A

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    B

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td][/td]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    A

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    B

    [/td]


    [td][/td]


    [td][/td]


    [td]

    C

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]



    Here's the strategy used (but not enough) :


    - In another sheet, make 3 times (1 for each letter "A", "B", "C", 10X10 cell criteria list with header week# (10,11,12...20), in the format of 1 criteria per row
    ex:
    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    10

    [/td]


    [td]

    11

    [/td]


    [td]

    12

    [/td]


    [td]

    13

    [/td]


    [td]

    14

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    B

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td]

    B

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td][/td]


    [td][/td]


    [td]

    B

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    B

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    B

    [/td]


    [/tr]


    [/TABLE]


    - Use function to convert week# to cell position
    - Use variable to store start (s1) and end (s2) position as number
    - Use the advanced filter macro


    Here's the problem


    - I get some empty row when i filter. For exemple if i want week 11 to 13 (B1 to D6), i will have emtpy criteria row and filter wont work correctly because he will take "blank" as a criteria, which i dont want.
    - Having the dynamic range cause me a lot more problem than i though. I tried something like:
    test_range = Range(Cells(1, s1), Cells(5, s2)) but it doesnt work...


    Here's the code

    Added to the blank row, i cant get the dynamic part working well either. I tried something like this:


    Code
    Dim test_range As RangeDim s1 as integerDim s2 as integers1 = range("Z10") 'just consider its correct value for nowS2 = range("Z11") 'just consider its correct value for nowSet test_range = Range(Cells(1, s1), Cells(6, s2)) 'here 1 is the first row and 6 is the last row of the criteria tableRange("A1:E7").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _Range(test_range), Unique:=False

    So to resume, i need help on how to get past empty row problem and dynamic colomn selection. Would be very appreciated!

  • Re: Advanced filter with dynamic critieria range


    Hello


    Welcome to Ozgrid.


    I am not giving you the complete solution, but rather the tools to get it working. With an advanced filter.
    Please go to my website (see below), then navigate to the English Excel articles and look for the article on "Filter out errors".
    A similar approach will work. This is good material so please spend some time to digest, and you will not complain about that effort :)

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!