Autofilter Several Columns And Then Top 25

  • excel sp2 2003 ,OS: XP

    hi all, this was addressed in april 17 by Go6 and has expired for response.

    i am having the exact same difficulty and the suggested fix does not seem to work.
    [ Menu -> Filter -> Show all vba = ActiveSheet.ShowAllData]
    quoting from the post:
    I have a list of data in a Worksheet, around 1900 rows and 20 columns. I would like to use AutoFilter to view/analyse this. Two columns are Market Cap (numeric) and Change Today ( Percentage).
    I would like to
    (a) filter for Market Cap > 120 (using Custom filter)
    (b) filter for Top 25 in the "Change Today" column
    Given that step (a) results in 300 entries, I thought that step (b) would autofilter these and leave me with 25 rows.
    However it actually leaves me with 2 rows. The reason for this seems to be that 23 of the 25 highest entries in "Percentage" column are for rows that have "Market Cap" less than 120, and were already filtered out in step (a)

    With wsLines.Range("C6")
            .AutoFilter Field:=4, Criteria1:="=" & strSeg
            .AutoFilter Field:=17, Criteria1:=">=" & dblLike
            .AutoFilter Field:=18, Criteria1:=">=" & strStartDate, Operator:=xlAnd, Criteria2:="<=" & strEndDate
            .AutoFilter Field:=16, Criteria1:="25", Operator:=xlTop10Items    
        End With

    here i filter 3 times, each time reducing the result set which ends up as 41 which i then want the top 25 of and i get 18

    in fact the ShowAllData method did not improve the problem at all. in fact it brings back unwanted cells from the first criteria.

    any ideas?

    ideally i prefer to use these useful MS built in's as i hate having to write tedious code to strip out the top X from a range.

  • Re: Autofilter Several Columns And Then Top 25


    If your code works on one sheet with autofilter, then you will never overcome the problem. You see, there can only be one instance of autofilter per sheet. This means that each line of code operates separately on the one instance of autofilter. After the code is executed, you have one set of data filtered by all four criteria.

    To overcome this, copy the results of one autofilter to another sheet, filter that data, copy the results to another sheet, filter, copy, filter, etc. Until you are satisfied.


  • Re: Autofilter Several Columns And Then Top 25

    thx jim,

    thats a great shame if it's indeed the case.

    i'll revert back to doing the 1st 3 filter operations in code (works ok)
    then copy results to another sheet and cut out the top X from there.

Participate now!

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