Autofilter Several Columns And Then Top 25

davidm · May 30th 2007

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)
========================================================

Code

With wsLines.Range("C6")
        .AutoFilter Field:=4, Criteria1:="=" & strSeg
        ActiveSheet.ShowAllData
        .AutoFilter Field:=17, Criteria1:=">=" & dblLike
        ActiveSheet.ShowAllData
        .AutoFilter Field:=18, Criteria1:=">=" & strStartDate, Operator:=xlAnd, Criteria2:="<=" & strEndDate
        ActiveSheet.ShowAllData
        .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.

JimFuller1 · May 30th 2007

Re: Autofilter Several Columns And Then Top 25

davidm,

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.

Jim

davidm · May 30th 2007

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.

Autofilter Several Columns And Then Top 25

Participate now!

Share